June 18, 2008 at 7:49 am
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page.
I am currently retrieving most of the needed fields using the following SQL statement:
PROCEDURE [dbo].[pr_getReportTickets]
@DateCreated nvarchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
tbl_Queue.num_TicketNumber AS TicketID
, tbl_Users.str_Name AS Technician
, tbl_Queue.str_QueueLocator AS TechNTID
, tbl_Queue.dat_ReceivedRequest AS dateCreated
, tbl_Queue.dat_DueDate AS DueDate
, tbl_Queue.str_TaskName AS TicketTitle
, tbl_Queue.str_Requestor AS RequestorNTID
--, I need most current dat_TimeStamp from tbl_Notes here for this record
--, I need most current int_PercentComplete from tbl_Notes here for this record
--, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record
, MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName
, tbl_Queue.str_TicketType AS TicketType
, tbl_Status.str_TaskStatus AS TicketStatus
, tbl_Severity.str_Priority
, tbl_Complexity.str_Complexity
, tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority
, tbl_Queue.dat_CompleteDate as DateCompleted
FROM
dbo.tbl_Queue
LEFT OUTER JOIN
MasterEmp.dbo.tbl_employee AS tbl_employee
ON
dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid
LEFT OUTER JOIN
tbl_Users
ON
tbl_Queue.str_QueueLocator = tbl_Users.str_ntid
LEFT OUTER JOIN
tbl_Status
ON
tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD
LEFT OUTER JOIN
tbl_Severity
ON
tbl_Queue.str_Severity = tbl_Severity.str_Severity
LEFT OUTER JOIN
tbl_Complexity
ON
tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID
WHERE
(dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102))
ORDER BY
TicketType,
tbl_Queue.str_Severity
I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber”. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked
I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.
1)The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC
2)The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutes
FROM tbl_Notes WHERE (num_TicketNumber = 49)
June 18, 2008 at 8:21 am
Look up the Max() and Sum() aggregate functions. You could use those in sub-queries (or CTEs - those are my preference) and then join to the sub-query/CTE.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 9:37 am
I understand how to do "sub queries" as in the bolded line below. What I do not understand is how to tell the T-SQL statement to which ticketnumber to select inside the sub query. Can you please post an example of what you are talking about?
Thanks
SELECT
tbl_Queue.num_TicketNumber AS TicketID
, tbl_Users.str_Name AS Technician
, tbl_Queue.str_QueueLocator AS TechNTID
, tbl_Queue.dat_ReceivedRequest AS dateCreated
, tbl_Queue.dat_DueDate AS DueDate
, tbl_Queue.str_TaskName AS TicketTitle
, tbl_Queue.str_Requestor AS RequestorNTID
, (SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC) as dat_TimeStamp
--, I need most current int_PercentComplete from tbl_Notes here for this record
--, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record
, MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName
, tbl_Queue.str_TicketType AS TicketType
, tbl_Status.str_TaskStatus AS TicketStatus
, tbl_Severity.str_Priority
, tbl_Complexity.str_Complexity
, tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority
, tbl_Queue.dat_CompleteDate as DateCompleted
June 18, 2008 at 10:33 am
Don't do it that way. That turns it into RBAR (Row-By-Agonizing-Row), which will slow it down big time.
What I meant was something like this:
;with NotesTopStamp (TNumber, TopStamp) as -- CTE for top timestamp
(select num_TicketNumber, max(dat_TimeStamp)
from dbo.tbl_Notes
group by num_TicketNumber)
SELECT
tbl_Queue.num_TicketNumber AS TicketID
, tbl_Users.str_Name AS Technician
, tbl_Queue.str_QueueLocator AS TechNTID
, tbl_Queue.dat_ReceivedRequest AS dateCreated
, tbl_Queue.dat_DueDate AS DueDate
, tbl_Queue.str_TaskName AS TicketTitle
, tbl_Queue.str_Requestor AS RequestorNTID
, TopStamp, -- From CTE
--, I need most current dat_TimeStamp from tbl_Notes here for this record
--, I need most current int_PercentComplete from tbl_Notes here for this record
--, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record
, MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName
, tbl_Queue.str_TicketType AS TicketType
, tbl_Status.str_TaskStatus AS TicketStatus
, tbl_Severity.str_Priority
, tbl_Complexity.str_Complexity
, tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority
, tbl_Queue.dat_CompleteDate as DateCompleted
FROM
dbo.tbl_Queue
LEFT OUTER JOIN
MasterEmp.dbo.tbl_employee AS tbl_employee
ON
dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid
LEFT OUTER JOIN
tbl_Users
ON
tbl_Queue.str_QueueLocator = tbl_Users.str_ntid
LEFT OUTER JOIN
tbl_Status
ON
tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD
LEFT OUTER JOIN
tbl_Severity
ON
tbl_Queue.str_Severity = tbl_Severity.str_Severity
LEFT OUTER JOIN
tbl_Complexity
ON
tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID
LEFT OUTER JOIN -- Join to CTE
NotesTopStamp
ON
tbl_Queue.num_TicketNumber = NotesTopStamp.TNumber
WHERE
(dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102))
ORDER BY
TicketType,
tbl_Queue.str_Severity
I added a CTE at the top to find the maximum timestamp for each ticket number. Then join that to the main select. That will make it much faster.
You can do the same thing for the other columns you need. Just add more CTEs at the top (separate them with commas, and don't use "with" except on the first one).
If you're not comfortable with that, put the table structure for your notes table in here, and I'll see if I can help you with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 10:44 am
Thank you very much for your help with this.
I just added this to my stored procedure and it worked perfectly. I guess that I need to read more about these CTE's.
I have another web page which is building the T-SQL statement creating the WHERE clause based on information selected in four drop down lists. The web page is executing the T-SQL statement directly (not with a stored procedure.) Other than the where clause, the T-SQL statement is the same as in my above code. Can I use CTE's in this fashion (directly in the web page) or are they limited to Stored Procedures?
Thanks again for your help.
June 18, 2008 at 10:48 am
CTEs can be used in dynamic SQL scripts. Just has to be SQL 2005, pretty much.
If you want to look them up in Books Online, search for "Common Table Expression". They're pretty useful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply