Doing a daily summary with SQL

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;

Advertisements

4 thoughts on “Doing a daily summary with SQL

  1. If no requests were opened on a particular date, the query won’t show the number of closed requests either.


    select
    report_date,
    sum(qty_opened) as qty_opened,
    sum(qty_closed) as qty_closed
    from
    (
    select
    trunc(CreatedTimestamp) as report_date, /* keep date only */
    count(1) as qty_opened,
    0 as qty_closed
    from HelpRequest
    where CreatedTimestamp between '2011-01-06' and '2011-01-13'
    group by trunc(CreatedTimestamp)

    union

    select
    trunc(ClosedTimestamp) as report_date,
    0 as qty_opened,
    count(1) as qty_closed
    from HelpRequest
    where ClosedTimestamp between '2011-01-06' and '2011-01-13'
    group by trunc(ClosedTimestamp)
    )
    group by report_date
    order by report_date

  2. No worries.

    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


    with
    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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s