Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

count events on date Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 4:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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
Post #1569749
Posted Monday, May 12, 2014 4:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:56 AM
Points: 632, Visits: 1,152
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

Post #1569764
Posted Monday, May 12, 2014 5:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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
Post #1569773
Posted Monday, May 12, 2014 5:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
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
Post #1569774
Posted Monday, May 12, 2014 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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?
Post #1569816
Posted Monday, May 12, 2014 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
;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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1569854
Posted Monday, May 12, 2014 9:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
Mike Saunders NL (5/12/2014)
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?


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
Post #1569926
Posted Monday, May 12, 2014 9:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
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?


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


Blimey! You've tested it already?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1569927
Posted Monday, May 12, 2014 10:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 23,064, Visits: 31,592
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1569956
Posted Monday, May 12, 2014 10:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 23,064, Visits: 31,592
Here are the execution plans and a spreadsheet with the results tab from Plan Explorer.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)


  Post Attachments 
Events.sqlplan (2 views, 212.15 KB)
PlanExplorerResultsEvents.xlsx (8 views, 9.54 KB)
Post #1569960
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse