CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012
taylorcs612
taylorcs612

Reputation: 21

Convert start and end dates to normalized table

I have a table with customer records, with each customer having a start date and end date. I'm looking for the most efficient way to convert this into a table that counts the number of active customers for each day. For example:

Existing table (Table A):

Customer - Start Date - End Date 
A        - 1/1/2017   - 1/3/2017 
B        - 1/2/2017   - 1/5/2017

What I need (Table B):

Date     - Customer_Count
1/1/2017 - 1
1/2/2017 - 2
1/3/2017 - 2
1/4/2017 - 1
1/5/2017 - 1

The method I'm using right now is simply joining a date reference table to the customer table, and then grouping by the reference date column. While this method works, the customer table is very large, and there are additional conditions I want to be able to apply (i.e., the geography of the customer, product, etc.) which will additionally impact performance.

Appreciate the help!

Upvotes: 1

Views: 647

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

A tally/calendar table would do the trick, but an ad-hoc tally table in concert with a Cross Apply may help as well

Example

Select Date
      ,Customer_Count = count(*)
 From  YourTable A
 Cross Apply (
                Select Top (DateDiff(DD,[Start Date] ,[End Date] )+1) Date=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[Start Date]) 
                 From  master..spt_values 
             ) B
 Group By Date
 Order By Date

Returns

Date        Customer_Count
2017-01-01  1
2017-01-02  2
2017-01-03  2
2017-01-04  1
2017-01-05  1

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can generate dates using custom table and the do cross apply as below:

select RowN as [Date], count(*) as Customer_Count from #yourcust cross apply
(
    select top (datediff(day, startdate, enddate)+1) rowN = dateadd(day, row_number() over (order by s1.name) -1 , startdate) from master..spt_values s1,master..spt_values s2
) a
group by RowN

Output 

+------------+----------------+
|    Date    | Customer_Count |
+------------+----------------+
| 2017-01-01 |              1 |
| 2017-01-02 |              2 |
| 2017-01-03 |              2 |
| 2017-01-04 |              1 |
| 2017-01-05 |              1 |
+------------+----------------+

Upvotes: 1

Related Questions