January 6, 2016 at 4:54 pm
Hi Guys,
I have a table that records a certain event happening several times a day for most days over a 1 month period.
I would like to have a stored procedure that interrogates this table and returns the number of events for each day between 2 dates.
I could do a simple grouping of the days and have an aggregate count of the events, but I'm also interested in listing those days where 0 events happened and for that to be returned as part of the overall list between the 2 input dates.
So to recap I'd like the returned list to show every date between the 2 input dates and a number of events that happened on each of the days including 0 events for those days where no event happened.
EventDate NumberOfEvents
--------------------------------------
01/01/2016 12
02/01/2016 4
03/01/2016 0
04/01/2016 0
05/01/2016 15
...... etc.
I found out how to create a list of dates between 2 dates with the following stored procedure maybe this could be utilised somehow?
CREATE PROCEDURE [dbo].[ListDates]
AS
declare @startDate date
declare @endDate date
select @startDate = '20160101'
select @endDate = '20160131'
;with dateRange as
(
select dt = dateadd(dd, 0, @startDate)
where dateadd(dd, 0, @startDate) <= @endDate
union all
select dateadd(dd, 1, dt)
from dateRange
where dateadd(dd, 1, dt) <= @endDate
)
select *
from dateRange
If you could point me in the right direction that would be greatly appreciated.
Thanks in advance!
January 6, 2016 at 5:01 pm
Nothing to it. Use a Calendar Table, and then outer join it to your Events table.
SELECT c.CalendarDate, Count(e.EventID)
FROM Calendar c LEFT JOIN Events e ON c.CalendarDate = e.EventDate
WHERE c.CalendarDate>=@StartDate AND c.CalendarDate<=@EndDate
GROUP BY c.CalendarDate
Not sure how I missed that... kinda helps to filter out all the unimportant dates!
January 6, 2016 at 5:26 pm
Hey Piet thank you for your prompt reply.
Does that mean I have to create a calendar table that's already pre-populated with dates?
Is there not a way that TSQL can recursively generate a bunch of dates on the fly?
I connected your example to my table and I had the following error...
Msg 208, Level 16, State 1, Procedure ListDates, Line 7
Invalid object name 'Calendar'.
(1 row(s) affected)
Thanks
January 6, 2016 at 8:03 pm
joe-584802 (1/6/2016)
Hey Piet thank you for your prompt reply.Does that mean I have to create a calendar table that's already pre-populated with dates?
Is there not a way that TSQL can recursively generate a bunch of dates on the fly?
I connected your example to my table and I had the following error...
Msg 208, Level 16, State 1, Procedure ListDates, Line 7
Invalid object name 'Calendar'.
(1 row(s) affected)
Thanks
You can... But a pre-populated Calendar table is a VERY handy thing to have around.
If you do want to create your calendar table on the fly, use a tally table rather than recursion to do it. The performance of recursive CTEs is horrible for that sort of thing.
Here's a quick example...
DECLARE
@BegDate DATE,
@EndDate DATE;
SELECT
@BegDate = '2015-12-10',
@EndDate = '2016-01-15';
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Calendar (dt) AS (
SELECT TOP (DATEDIFF(dd, @BegDate, @EndDate) + 1)
DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1, @BegDate)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
SELECT
c.dt
FROM
cte_Calendar c
January 6, 2016 at 11:22 pm
Thank you so much guys for your useful tips and SQL snippets.
I will follow your advice and create a pre-populated calendar table, it makes total sense.
Cheers!
January 6, 2016 at 11:23 pm
Quick solution in line with Jason's suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(SD_ID,SD_DATE) AS
( SELECT SD_ID,CONVERT(DATE,SD_DATE,120) FROM
( VALUES
( 1,'2016-01-01')
,( 2,'2016-01-01')
,( 3,'2016-01-01')
,( 4,'2016-01-01')
,( 5,'2016-01-01')
,( 6,'2016-01-03')
,( 7,'2016-01-03')
,( 8,'2016-01-03')
,( 9,'2016-01-03')
,( 10,'2016-01-03')
,( 11,'2016-01-03')
,( 12,'2016-01-04')
,( 13,'2016-01-04')
,( 14,'2016-01-04')
,( 15,'2016-01-04')
,( 16,'2016-01-04')
,( 17,'2016-01-04')
,( 18,'2016-01-07')
,( 19,'2016-01-07')
,( 20,'2016-01-07')
,( 21,'2016-01-07')
,( 22,'2016-01-07')
,( 23,'2016-01-07')
,( 24,'2016-01-07')
,( 25,'2016-01-07')
,( 26,'2016-01-07')
) AS X(SD_ID,SD_DATE)
)
, T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,DATES(MIND,DAY_COUNT) AS
(
SELECT
CONVERT(DATE,MIN(SD.SD_DATE),120) AS MIND
,DATEDIFF(DAY,MIN(SD.SD_DATE),MAX(SD.SD_DATE)) + 1 AS DAY_COUNT
FROM SAMPLE_DATA SD
)
,NUMDAYS(NDAY) AS
(
SELECT
TOP((SELECT DAY_COUNT FROM DATES)) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1,D.MIND) AS NDAY
FROM DATES D
CROSS JOIN T T1 CROSS JOIN T T2 CROSS JOIN T T3 CROSS JOIN T T4 CROSS JOIN T T5
)
SELECT
ND.NDAY AS EventDate
,COUNT(SD.SD_ID) AS NumberOfEvents
FROM NUMDAYS ND
LEFT OUTER JOIN SAMPLE_DATA SD
ON ND.NDAY = SD.SD_DATE
GROUP BY ND.NDAY
ORDER BY ND.NDAY;
Output
EventDate NumberOfEvents
---------- --------------
2016-01-01 5
2016-01-02 0
2016-01-03 6
2016-01-04 6
2016-01-05 0
2016-01-06 0
2016-01-07 9
January 6, 2016 at 11:37 pm
Thanks Eirikur, that's awesome...!
Thank you for taking the trouble to give me such a comprehensive example, you've given me another road to go down which I'll pursue.
You are all so generous with your time!
Many thanks,
Joe
January 7, 2016 at 6:52 am
Joe - Thanks for the feedback! Not many people provide feedback or even bother to say "thank you"... Just wanted to let you know that it's appreciated. 🙂
Since it wasn't explicitly mentioned... One of the most important/useful reasons to maintain a permanent calendar table is the fact that it allows you to manually track holidays or any other type of date that isn't easily determined on the fly. This come in handy when you're asked to calculate the number of "actual working days" between two dates.
January 7, 2016 at 7:28 am
joe-584802 (1/6/2016)
Thanks Eirikur, that's awesome...!Thank you for taking the trouble to give me such a comprehensive example, you've given me another road to go down which I'll pursue.
You are all so generous with your time!
Many thanks,
Joe
Echoing Jason, thanks Joe for the feedback.
😎
January 7, 2016 at 2:35 pm
This might be slightly off-topic, but the other auxiliary table that's super handy is a numbers table. (it can be virtual, like an iTVF) that has numbers from 1 to whatever. Jeff Moden has a really nice article on it. Definitely worth a read. The "Numbers" or "tally" table and how it replaces a loop[/url].
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply