May 12, 2014 at 4:26 am
Hello all,
I am trying to solve a problem. I have a tabel containing some events. Those events all have a start date and an end date.
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
)
INSERT INTO #events VALUES (1,'testevent 1','2014-05-02','2014-05-30');
INSERT INTO #events VALUES (2,'testevent 2','2014-05-09','2014-05-20');
INSERT INTO #events VALUES (3,'testevent 3','2014-05-10','2014-05-15');
INSERT INTO #events VALUES (4,'testevent 4','2014-05-02','2014-05-25');
SELECT eventID, eventname, startdate, enddate
FROM #events
drop table #events
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.
Is there a common way to solve this. Any help is welcome!
Thanks
May 12, 2014 at 4:54 am
I just written this as per my understanding
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
)
INSERT INTO #events VALUES (1,'testevent 1','2014-05-02','2014-05-30');
INSERT INTO #events VALUES (2,'testevent 2','2014-05-09','2014-05-20');
INSERT INTO #events VALUES (3,'testevent 3','2014-05-10','2014-05-15');
INSERT INTO #events VALUES (4,'testevent 4','2014-05-02','2014-05-25');
SELECT eventID, eventname, startdate, enddate
FROM #events
Declare @StartDate aS Date = '2014-01-01'
, @EndDate as Date = '2014-12-31'
;With cteCalender
AS
(
Select DATEADD(dd,N,@StartDate) AS cDate
, Month(DATEADD(dd,N,@StartDate)) AS cMonth
From (
Select top 366 row_number() over (Order by (select null)) - 1 AS N
from sys.all_columns c
cross join sys.all_columns cc
) Tally
Where DATEADD(dd,N,@StartDate) <= @EndDate
)
select *
from cteCalender c
Left join #events e on c.cDate = e.startdate
Where c.cMonth = 5
drop table #events
Hope it helps
May 12, 2014 at 5:08 am
Thanks, but that was not what I wanted to show. What I want is a table that contains two field.
Date and the number of events that are running on that date. In your solution you only show the when an event starts.
E.g. event 1 runs from 2014-5-2 to 2014-5-30. In this case I want to see in all dates between the start and end date that that event counts for 1.
In case multiple events are running on a date I want to see the total number of events running on that date.
Hope this explains my case further.
Thanks
May 12, 2014 at 5:13 am
Here is one way of doing this
😎
USE tempdb;
GO
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
)
INSERT INTO #events VALUES (1,'testevent 1','2014-05-02','2014-05-30');
INSERT INTO #events VALUES (2,'testevent 2','2014-05-09','2014-05-20');
INSERT INTO #events VALUES (3,'testevent 3','2014-05-10','2014-05-15');
INSERT INTO #events VALUES (4,'testevent 4','2014-05-02','2014-05-25');
;WITH TN(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,EVENTS_PER_DAY AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY NM.N ORDER BY (SELECT NULL)) AS EVENT_RID
,COUNT(E.eventname) OVER (PARTITION BY NM.N ORDER BY (SELECT NULL)) AS EVENT_COUNT
,DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) AS EVENT_DAY
FROM
(
SELECT
MIN(E.startdate) AS FIRST_DATE
,MAX(E.enddate) AS LAST_DATE
FROM #events E
) AS ED
OUTER APPLY
(
SELECT TOP (DATEDIFF(DAY,ED.FIRST_DATE,ED.LAST_DATE) + 1) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM TN T1, TN T2
, TN T3, TN T4, TN T5, TN T6, TN T7, TN T8, TN T9
) AS NM
OUTER APPLY #events E
WHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
)
SELECT
EPD.EVENT_DAY
,EPD.EVENT_COUNT
FROM EVENTS_PER_DAY EPD
WHERE EPD.EVENT_RID = 1;
drop table #events
Results
EVENT_DAY EVENT_COUNT
---------- -----------
2014-05-02 2
2014-05-03 2
2014-05-04 2
2014-05-05 2
2014-05-06 2
2014-05-07 2
2014-05-08 2
2014-05-09 3
2014-05-10 4
2014-05-11 4
2014-05-12 4
2014-05-13 4
2014-05-14 4
2014-05-15 4
2014-05-16 3
2014-05-17 3
2014-05-18 3
2014-05-19 3
2014-05-20 3
2014-05-21 2
2014-05-22 2
2014-05-23 2
2014-05-24 2
2014-05-25 2
2014-05-26 1
2014-05-27 1
2014-05-28 1
2014-05-29 1
2014-05-30 1
May 12, 2014 at 6:36 am
Hi, this works! Thanks
The only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
May 12, 2014 at 7:54 am
;WITH DateRange AS (
SELECT EventDate
FROM (
SELECT
FirstDate = DATEADD(month,DATEDIFF(month,0,MIN(startdate)),0),
LastDate = DATEADD(month,1+DATEDIFF(month,0,MAX(enddate)),-1)
FROM #events
) e
CROSS APPLY (
SELECT TOP(1 + DATEDIFF(day,e.FirstDate,e.LastDate))
EventDate = DATEADD(day,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,FirstDate)
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n), -- 10 rows
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n), -- 100 rows
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n) -- 1000 rows
) x
)
SELECT r.EventDate, x.EventCount
FROM DateRange r
OUTER APPLY (
SELECT EventCount = COUNT(eventID)
FROM #events e
WHERE r.EventDate BETWEEN e.startdate AND e.enddate
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2014 at 9:42 am
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events E
WHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events E
on DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
May 12, 2014 at 9:44 am
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events EWHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events Eon DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
Blimey! You've tested it already?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2014 at 10:17 am
Here is what I have seen in a couple of separate threads already, windowing functions aren't always the best solution to solving a problem.
May 12, 2014 at 10:24 am
Here are the execution plans and a spreadsheet with the results tab from Plan Explorer.
May 12, 2014 at 11:05 am
ChrisM@Work (5/12/2014)
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events EWHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events Eon DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
Blimey! You've tested it already?
I had to, it has been a slow code day for me. In fact, when I saw your code I thought "E tu Chris":-D as Lynn Pettis has been very close to humiliating me with much faster code on few occasions today.
😎
May 12, 2014 at 11:23 am
Eirikur Eiriksson (5/12/2014)
ChrisM@Work (5/12/2014)
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events EWHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events Eon DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
Blimey! You've tested it already?
I had to, it has been a slow code day for me. In fact, when I saw your code I thought "E tu Chris":-D as Lynn Pettis has been very close to humiliating me with much faster code on few occasions today.
😎
I don't mean to humiliate anyone, just like to try and provide code that is scalable and efficient.
May 12, 2014 at 11:46 am
Lynn Pettis (5/12/2014)
Eirikur Eiriksson (5/12/2014)
ChrisM@Work (5/12/2014)
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events EWHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events Eon DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
Blimey! You've tested it already?
I had to, it has been a slow code day for me. In fact, when I saw your code I thought "E tu Chris":-D as Lynn Pettis has been very close to humiliating me with much faster code on few occasions today.
😎
I don't mean to humiliate anyone, just like to try and provide code that is scalable and efficient.
Not taking it badly either, I really appreciate the collaborative effort.
😎
May 13, 2014 at 1:48 am
Eirikur Eiriksson (5/12/2014)
Lynn Pettis (5/12/2014)
Eirikur Eiriksson (5/12/2014)
ChrisM@Work (5/12/2014)
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! ThanksThe only thing that is missing is that the days with no events are not mentioned. Is there a way to display these days with an eventcount of 0?
To get the 0 event days change
OUTER APPLY #events EWHERE DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
to
LEFT OUTER JOIN #events Eon DATEADD(DAY,(NM.N - 1),ED.FIRST_DATE) BETWEEN E.startdate AND E.enddate
But you should use Chris's code, it is quicker than mine:ermm:
😎
Blimey! You've tested it already?
I had to, it has been a slow code day for me. In fact, when I saw your code I thought "E tu Chris":-D as Lynn Pettis has been very close to humiliating me with much faster code on few occasions today.
😎
I don't mean to humiliate anyone, just like to try and provide code that is scalable and efficient.
Not taking it badly either, I really appreciate the collaborative effort.
😎
Precisely! You should see what happens with ssc's favourite string splitter over the years :hehe:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 13, 2014 at 2:31 am
Guys, Funny to see your competitive contribution to my problem. Learned a lot, thanks again!:-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply