I have a requirement to write a stored procedure that accepts a start date, end date and a frequency (day, week, month, quarter, year) and outputs a result set based on those parameters. Obviously, the simple part is the query by date range, but how do you group by frequency?
So if have a set of raw data like this:
Date Count
---------------------
11/15/2011 6
12/16/2011 9
12/17/2011 2
12/18/2011 1
12/18/2011 4
And I call my stored proc like this:
sp_Report '1/1/2011', '12/31/2011', 'week'
I would expect results like this:
WeekOf Count
---------------------
11/19/2011 6
12/17/2011 11
12/24/2011 5
There are a couple of questions here:
1) How do I determine the date for the end of the week (week ending on Sunday)?
2) How do I group by that WeekOf date range?
Something like this should work.
The following script represents the output in a unified way: it shows period's start and end dates as well as the total count for the period.
That has also determined the ways of finding the values to group by. Basically, you can see three distinct patterns: one for the
'day'
frequency , another one for'week'
and still another for all the other frequency types.The first one is simplest: both PeriodStart and PeriodEnd are just
Date
.For weeks, I'm using a quite well known trick, whereby the first day of week is derived from the given date by subtracting from it a value that is one less than its weekday number. The end of the week is found similarly: we are merely adding
6
to the same expression.Months, quarters and years are grouped in the following manner. The integer number of corresponding units between the zero date and the given date is added back to the zero date. That gives us the beginning of the period. The end is found very similarly, only we are adding the number that is one greater than the difference. That produces the beginning of the next period, so we are then subtracting one day, which gives us the correct ending date.
EXEC spReport '1/1/2011', '12/31/2011', 'day'
:EXEC spReport '1/1/2011', '12/31/2011', 'week'
:EXEC spReport '1/1/2011', '12/31/2011', 'month'
:EXEC spReport '1/1/2011', '12/31/2011', 'quarter'
:EXEC spReport '1/1/2011', '12/31/2011', 'year'
:Note: From MSDN: