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;
4 thoughts on “Doing a daily summary with SQL”
If no requests were opened on a particular date, the query won’t show the number of closed requests either.
sum(qty_opened) as qty_opened,
sum(qty_closed) as qty_closed
trunc(CreatedTimestamp) as report_date, /* keep date only */
count(1) as qty_opened,
0 as qty_closed
where CreatedTimestamp between '2011-01-06' and '2011-01-13'
group by trunc(CreatedTimestamp)
trunc(ClosedTimestamp) as report_date,
0 as qty_opened,
count(1) as qty_closed
where ClosedTimestamp between '2011-01-06' and '2011-01-13'
group by trunc(ClosedTimestamp)
group by report_date
order by report_date
Thanks nedoboi for the bug fix and posting a tidier version – I see you added a trunc function too.
I’ve updated my post with your fixes.
Surprising how MSSQL doesn’t have trunc. In Oracle it’s a built-in, it also has another parameter – what to truncate the date to, for example trunc(date,’month’).
By the way, you can still use a join (full outer join to be precise), something like
t_o as (select ...),
t_c as (select ...)
select trunc_date, qty_o, qty_c
from t_o full outer join t_c on (trunc_date)
Thx, this really helped me