CODEWITHSUNDEEP
sqlsql-server
baineschile
baineschile

Reputation: 149

SQL - Count total IDs each day between dates

Here is what my data looks like

ID       StartDate        EndDate
1        1/1/2019         1/15/2019
2        1/10/2019        1/11/2019
3        2/5/2020         3/10/2020
4        3/10/2019        3/19/2019
5        5/1/2020         5/4/2020

I am trying to get a list of every date in my data set,and how many IDs fall in that time range, aggregated to the date level. So for ID-1, it would be in the records for 1/1/2019, 1/2/2019...through 1/15/2019.

I am not sure how to do this. All help is appreciated.

Upvotes: 0

Views: 63

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

If you don't have a calendar table (highly recommended), you can perform this task with an ad-hoc tally table in concert with a CROSS APPLY

Example

Declare @YourTable Table ([ID] varchar(50),[StartDate] date,[EndDate] date)  
Insert Into @YourTable Values 
 (1,'1/1/2019','1/15/2019')
,(2,'1/10/2019','1/11/2019')
,(3,'2/5/2020','3/10/2020')
,(4,'3/10/2019','3/19/2019')
,(5,'5/1/2020','5/4/2020')


Select A.ID
      ,B.Date
 From  @YourTable A
 Cross Apply (
                Select Top (DateDiff(DAY,A.[StartDate],A.[EndDate])+1) Date=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),A.[StartDate])  
                From  master..spt_values n1,master..spt_values n2
                ) B

Returns

ID  Date
1   2019-01-01
1   2019-01-02
1   2019-01-03
1   2019-01-04
1   2019-01-05
1   2019-01-06
1   2019-01-07
1   2019-01-08
1   2019-01-09
1   2019-01-15
2   2019-01-10
2   2019-01-11
....
5   2020-05-01
5   2020-05-02
5   2020-05-03
5   2020-05-04

Upvotes: 1

Related Questions