September 28th, 2009Sequencing Dates in SQL
“What were the dates of all the Mondays in the last 5 months?”
“How many Fridays were there between this date range?”
“What were the dates of the paydays in the last 10 months?”
Do any of these types of questions sound familiar? If you’ve ever worked with these types of date time questions in SQL, you would know that it can be VERY challenging.
At the hospital we use Cisco Unified Communications Manager (a.k.a CallManager).
The directors desired a report to break down various call statistics to one of their clinics. They were hoping this information would give them insight into how to handle call load.
One particular question was a tricky one: “What is the average call per days of the week, per hour, for the last three months?”.
For example: “What was the average call count, per hour, for all the Mondays in the last 3 months?”
Thanks to newly discovered dimensional tables, I had the breakdown per hour figured out, however, I was stumped on how to group it for average Mondays, Tuesdays, Wednesdays, etc.
That’s why I was thrilled to discover this solution by Peter Larsson, titled How Many More Mondays Until I Retire?
Essentially, Peter suggests creating a scalar SQL function that returns a table of dates between a specified date range. This opens a range of possibilities as far as dealing with tricky date queries.
Here is Peter’s function:
CREATE FUNCTION dbo.fnSeqDates
(
@LowDate DATETIME,
@HighDate DATETIME
)
RETURNS @Dates TABLE
(
SeqDate DATETIME
)
AS
BEGIN
DECLARE @Temp DATETIME
IF @LowDate > @HighDate
SELECT @Temp = @LowDate,
@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
ELSE
SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)
INSERT @Dates
(
SeqDate
)
VALUES (
@LowDate
)
WHILE @@ROWCOUNT > 0
INSERT @Dates
(
SeqDate
)
SELECT DATEADD(dd, n.Items, d.SeqDate)
FROM @Dates d
CROSS JOIN (
SELECT COUNT(SeqDate) Items
FROM @Dates
) n
WHERE DATEADD(dd, n.Items, d.SeqDate) <= @HighDate
RETURN
END
I used the function in my stored procedure like this:
@StartDate datetime,
@EndDate datetime,
@Extensions varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Time_Value TABLE (
RangeName varchar(18) UNIQUE,
Value int UNIQUE
)
-- This section is for setting up the dimensional table for the hours breakdown
INSERT INTO @Time_Value VALUES ('00',0)
INSERT INTO @Time_Value VALUES ('01',1)
INSERT INTO @Time_Value VALUES ('02',2)
INSERT INTO @Time_Value VALUES ('03',3)
INSERT INTO @Time_Value VALUES ('04',4)
INSERT INTO @Time_Value VALUES ('05',5)
INSERT INTO @Time_Value VALUES ('06',6)
INSERT INTO @Time_Value VALUES ('07',7)
INSERT INTO @Time_Value VALUES ('08',8)
INSERT INTO @Time_Value VALUES ('09',9)
INSERT INTO @Time_Value VALUES ('10',10)
INSERT INTO @Time_Value VALUES ('11',11)
INSERT INTO @Time_Value VALUES ('12',12)
INSERT INTO @Time_Value VALUES ('13',13)
INSERT INTO @Time_Value VALUES ('14',14)
INSERT INTO @Time_Value VALUES ('15',15)
INSERT INTO @Time_Value VALUES ('16',16)
INSERT INTO @Time_Value VALUES ('17',17)
INSERT INTO @Time_Value VALUES ('18',18)
INSERT INTO @Time_Value VALUES ('19',19)
INSERT INTO @Time_Value VALUES ('20',20)
INSERT INTO @Time_Value VALUES ('21',21)
INSERT INTO @Time_Value VALUES ('22',22)
INSERT INTO @Time_Value VALUES ('23',23)
SELECT
CASE
WHEN DATEPART(weekday,ReportShortDateTime) = 1 THEN 'Sunday'
WHEN DATEPART(weekday,ReportShortDateTime) = 2 THEN 'Monday'
WHEN DATEPART(weekday,ReportShortDateTime) = 3 THEN 'Tuesday'
WHEN DATEPART(weekday,ReportShortDateTime) = 4 THEN 'Wednesday'
WHEN DATEPART(weekday,ReportShortDateTime) = 5 THEN 'Thursday'
WHEN DATEPART(weekday,ReportShortDateTime) = 6 THEN 'Friday'
WHEN DATEPART(weekday,ReportShortDateTime) = 7 THEN 'Saturday'
END AS Day_Name, RangeName AS Hour_Range,
COUNT(ReportShortDateTime) AS Calls,
(ROUND((CAST(COUNT(ReportShortDateTime) AS Float) / CAST((SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)) AS Float)), 1)) AS Average_Calls
FROM [CALLMANAGER] INNER JOIN
@Time_Value ON DATEPART(hour, ReportDateTimeOrigination) = Value
WHERE ReportShortDateTime BETWEEN @StartDate AND @EndDate AND
originalCalledPartyNumber IN (Select Convert(varchar(50),Value) FROM dbo.fnSplit(@Extensions, ','))
GROUP BY DATEPART(weekday,ReportShortDateTime), RangeName, Value
ORDER BY DATEPART(weekday,ReportShortDateTime), Value
END
As you can see in the query, I use the function like this:
SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)
In other words, I’m saying: “Give me the count of days, between this specified date range, that are Mondays, Tuesdays, Wednesdays, etc.”
