I need to combine the details of two tables and narrow the data included.
in my tutor table i have
tutorLastName, tutorSessions and tutorSkills
in my term table i have
week, date, session_Name, tutorName
i need to combine the tales to show:
(from the term table) week, date,session_Name, tutorName,
(from the tutor table) tutorSkills.
i then need to narrow the table down so it shows only tutors with guitar skills and the term dats when the session_name is empty
i can run the two searches sepereately but cannot combine them i am tryin to use Where date = "whateverdate" AND session_name is null
AND tutorSkills = guitar
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
I had noticed that certain expressions won't work in details. Is this true ? when I tried using an IIF expression, I always get a blank or an empty field even if it was a text. could someone clarify this ? I don't know whether it is just me or is it a limitation.
I want to make a report with a table who gives some figures of a family and elements of her subfamily. For example, for the Fruit family I have like subfamily the apple, the pear, the banana. I tried to make a table with a group but to aggregate some figures of the subfamily dataset, calculations are complicated.
So I make two report: - one for a dataset for the subfamily with a parameter (the family). - one for the dataset of the family.
In the second, I want to put the under-report (the first) in the details of the table while passing in parameter the family.
But, I have an error when I want to see the result:" Error: Impossible to post the under-report."
I've seen a number of questions on combining rows, but not one exactly like this. I have a solution, but I'd like to know if there are other ways. I'd like to select and combine rows from a table. Here's a simplified version of the table: tab1 key date status 1 1/1/06 stat1 1 1/2/06 stat2 1 1/3/06 stat3 1 1/4/06 stat4 2 1/1/06 stat1 2 1/2/06 stat2
And the desired results: key date status prevstatus 1 1/1/06 stat1 null 1 1/2/06 stat2 stat1 1 1/3/06 stat3 stat2 1 1/4/06 stat4 stat3 2 1/1/06 stat1 null 2 1/2/06 stat2 stat1
Here's the simplified version of the solution: select a.*,b.status prevstatus from tab1 a left join tab1 b on a.key = b.key and b.date = (select max(date) from tab1 c where a.key = c.key and a.date > c.date )
I faced a problem when using Northwind sample Database. On of the table in the database is 'Order Details'. I am unable to query on the table because of the space in the name ('Order'<space> 'Details') of the table. When I remove the space in the name, the reports associated in Cognos are not working properly. Let me know whether there is any purpose to put the name like that and if I want to see the data in query mode how it is possible.
Can anyone tell me is it possible to put static data (a string) inside details section of a table? I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS. But thay are invisible when I open my report using ReportViewer in my app. So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select clientnumber,count(distinct clientnumber) as cccnt, 0 as ckcnt, 0 as cacnt from dbo.purchases where orderdate >= 20120101 and orderdate <= 20121231 and payment_type = 'CC' group by clientnumber;
OUTPUT currently looks like this: 1234 2 0 0 1234 0 1 0 1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?: 1234 2 1 4 Y
I have came across a table in SQL server 2000 which named 'Order Details' in the sample 'Northwind' database which is available with the product. Am using the eval version . Generally no table name exist with a 'space' between the words. But the table 'Order Details' exist in the Northwind sample database.
Due to the naming convention i can't run sql queries on that. Is anyone aware of this type of issues.
1. Fact table with sales by customer, Territory ID etc 2. Table with targets per Territory ID
I want two combine them in one pivot table.
Pivot: Territory;Sales;Target
What I'm getting is the correct column with sales, but the overall sum of all territories in column for targets.Relationship is established between two tables via Territory ID
Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins @tblResults tr JOIN dbo.MarketPrice mp ON tr.Item = mp.Item AND tr.[Month] = mp.[Month] AND tr.[Year] = mp.[Year] AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50 The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28' The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50 The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
I am trying to create a matrix report to work similar to a pivot table, where when a user clicks on the count, the details are displayed. When I add the action to open a sub-report, how do I pass in the parameter values for the group that was selected? meaning if in row group- I have company name, and column group I have job title, when the count gets clicked, i need to pass in the company name for the row that was clicked along with the column group. How can I proceed?
SET @RowCnt = 1 SET @date = CONVERT(CHAR(10),GETDATE(),110) SET @ArchPath = '\D$EDATAWorkFoldersSendSendData' SELECT @TotalRows = count(*) FROM table1 --select @ArchPath
WHILE (@RowCnt <= @TotalRows) BEGIN SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt --PRINT @AccountNumber --test SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = ''' + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c' --PRINT @sql EXEC master..xp_cmdshell @sql SELECT @RowCnt = @RowCnt + 1 END
Well I made a bussiness object for registering users as well as logging them in. I dont know if my bussiness obect is screwed up or if its the database, or the SQL syntax or what. Please take a look at this. I am not getting any error messages, but nothing is being added to the server. I am using MSDE for the SQL Server.
Here's my bussiness object:
Imports System Imports System.Data imports System.Data.oledb
NameSpace LoveShare
Public Class UserDetails public UserID AS Integer public FirstName AS String public LastName AS String public UserName AS string public Password AS String public Address AS String public City AS String public State AS String public Zip As Integer public Email AS String End Class
Public Class User
Private objConn As New oledbConnection("Provider=sqloledb;Data Source=GARAGESALELOVESHARE;Initial Catalog=LoveShare1;User Id=sa;Password=notonthispost;")
Public function Login(strUsername AS String, strPassword As String) As Integer dim intID as integer dim objparam AS new oledbparameter dim objcmd AS oledbCommand
objCmd = new oledbCommand("dbo.SPLoginUser", objconn) objcmd.commandtype = commandtype.Storedprocedure
objparam = New oledbparameter("@UserName", oledbtype.Char) objParam.Value = strUserName objCmd.Parameters.Add(objParam)
objParam = New oledbParameter("@Password", OleDbType.Char) objParam.Value = strPassword objCmd.Parameters.Add(ObjParam)
Try objConn.Open intID = CType(objCmd.ExecuteScalar, Integer) objConn.Close Catch e As Exception Throw e End Try
If intID.toString = "" Then return 0 End if Return intID end function
Public Sub AddUser(objUser As UserDetails) Dim intId as integer Dim objReader As oledbdataReader Dim objCmdID As New oledbCommand("SELECT MAX(userID) FROM tblUsers", objconn) Dim objcmd AS New oleDBCommand("spAddUser", objConn) Dim objparam AS OleDbParameter objCmd.CommandType = CommandType.StoredProcedure
objParam = New oleDbParameter("@FirstName", oledbtype.Char) objParam.value = objuser.FirstName objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@LastName", oledbtype.Char) objParam.value = objuser.LastName objCmd.Parameters.add(objParam)
objParam = new oleDbParameter("@UserName", oledbtype.Char) objParam.Value = objUser.UserName objCmd.Parameters.Add(objParam)
objParam = New oleDbParameter("@Password", oledbtype.Char) objParam.value = objuser.Password objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Email", oledbtype.Char) objParam.value = objuser.Email objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Address", oledbtype.Char) objParam.value = objuser.Address objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@City", oledbtype.Char) objParam.value = objuser.City objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@State", oledbtype.Char) objParam.value = objuser.State objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Zip", oledbtype.Integer) objParam.value = objuser.Zip objCmd.Parameters.add(objParam)
Try objConn.Open ObjCmd.ExecuteNonQuery objUser.UserID = CType(objCmdID.ExecuteScalar, Integer) objConn.Close Catch e AS Exception Throw e End try
If objUser.UserID.ToString = "" then objuser.UserID = 25 End if End Sub End Class End NameSpace
Here's is my stored procedure for the registration:
Session("UserID") = objUserDetails.UserID FormsAuthentication.SetAuthCookie(objUserDetails.UserID, false) Response.Redirect("index.aspx") else lblMessage.text="Information entered incorrectly" End If End Sub
I have a database which has contact column eg. Mr Peter Smith
I am writing a new database which is to have three seperate columns.. saluation, first name and surname. What would be the best way to split the column up?? I was thinking on concentrating on the spaces??
Note: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...
Can somebody please go in depth detail what exactly is done in OLAP? i know it deals with the data warehouse end. but what does the SQL DBA do in the OLAP end? please elaborate. thanks.
How can i get the details of the error in sql server 2000 such where itoccurs in which line. I need something like i get in sql query analyzer.--Message posted via http://www.sqlmonster.com
