One of the account managers at work asked me if I could give her a summary of how many support requests are opened and closed each day.
Update 23 Jan 2011
Thanks to nedoboi for pointing out a bug with my code in the comments, and posting a fix!
Here’s a simplified version of our HelpRequest table.
CREATE TABLE [dbo].[HelpRequest]( [Id] [int] IDENTITY(1,1) NOT NULL, [CreatedTimestamp] [datetime] NOT NULL, [ClosedTimestamp] [datetime] NULL ) ON [PRIMARY]
Each HelpRequest has a CreatedTimestamp and a ClosedTimestamp which are both datetimes. One way to get the Date part of a datetime is like so:
SELECT CAST( FLOOR( CAST( CreatedTimestamp AS float) ) AS smalldatetime) AS OpenedDate
Let’s put that in a function.
CREATE FUNCTION truncateDate ( @date datetime ) RETURNS datetime AS BEGIN RETURN CAST( FLOOR( CAST( @date AS float) ) AS smalldatetime) END
Now let’s do a GROUP BY to get all the HelpRequests opened on a given date range:
SELECT dbo.truncateDate(CreatedTimestamp) AS Report_Date, COUNT(1) AS Qty_Opened FROM HelpRequest WHERE CreatedTimestamp BETWEEN '2010-01-01' AND '2011-01-13' GROUP BY dbo.truncateDate(CreatedTimestamp)
We can do the same thing, but for closed help requests. No need to show that code.
Finally, do a
join union to get the Open and the Closed for each day.
SELECT Report_Date, SUM(QTY_OPENED) AS Opened, SUM(QTY_CLOSED) AS Closed FROM ( SELECT dbo.truncateDate(CreatedTimestamp) AS Report_Date, COUNT(1) AS Qty_Opened, 0 AS QTY_CLOSED FROM HelpRequest WHERE CreatedTimestamp BETWEEN '2010-01-01' AND '2011-01-13' GROUP BY dbo.truncateDate(CreatedTimestamp) UNION SELECT dbo.truncateDate(ClosedTimestamp) AS Report_Date, 0 AS QTY_OPENED, COUNT(1) AS QTY_CLOSED FROM HelpRequest WHERE ClosedTimestamp BETWEEN '2010-01-01' AND '2011-01-13' GROUP BY dbo.truncateDate(CLOSEDTIMESTAMP) ) DATES GROUP BY Report_Date ORDER BY Report_Date
Mega-ugly, but it works. Too bad I don’t have a DBA to help me tidy it up! Feel free to write a comment with any suggestions.
Thanks again to nedoboi for the bug fix.
OK, final task is to parameterize the above query and then add it to SQL Server Reporting Services. A little bit of playing and, voila;