Create a row for each date in a range, and add 1 for each day within a date range for a record in SQL

Question

Suppose I have a date range, @StartDate = 2022-01-01 and @EndDate = 2022-02-01, and this is a reporting period.

In addition, I also have customer records, where each customer has a LIVE Date and a ServiceEndDate (or ServiceEndDate = NULL as they are an ongoing customer)

Some customers may have their Live Date and Service end date range extend outside of the reporting period range. I would only want to report for days that they were a customer in the period.

Name	LiveDate	ServiceEndDate
Tom	2021-10-11	2022-01-13
Mark	2022-11-13	2022-02-15
Andy	2022-01-02	2022-02-10
Rob	2022-01-09	2022-01-14

I would like to create a table where column A is the Date (iterating between every date in the reporting period) and column B is a sum of the number of customers that were a customer on that date.

Something like this

Date	NumberOfCustomers
2022-01-01	2
2022-01-02	3
2022-01-03	3
2022-01-04	3
2022-01-05	3
2022-01-06	3
2022-01-07	3
2022-01-08	3
2022-01-09	4
2022-01-10	4
2022-01-11	4
2022-01-12	4
2022-01-13	4
2022-01-14	3
2022-01-15	3

And so on until the end the @EndDate

Any help would be much appreciated, thanks.

Answer 1

I would personally suggest using a Tally, rather than an rCTE, as a Tally is significantly more performant.

SELECT *
INTO dbo.YourTable
FROM (VALUES('Tom ',CONVERT(date,'2021-10-11 '),CONVERT(date,'2022-01-13')),
            ('Mark',CONVERT(date,'  2022-11-13'),CONVERT(date,' 2022-02-15')),
            ('Andy',CONVERT(date,'  2022-01-02'),CONVERT(date,' 2022-02-10')),
            ('Rob ',CONVERT(date,'2022-01-09 '),CONVERT(date,'2022-01-14')))V(Name,LiveDate,ServiceEndDate);
GO
SELECT *
FROM dbo.YourTable;
GO
DECLARE @StartDate date = '20220101',
        @EndDate date = '20220201';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --up to 1,000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS Date
    FROM Tally T)
SELECT D.Date,
       COUNT(YT.[Name]) AS NumberOfCustomers
FROM Dates D
     LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.LiveDate
                               AND (D.[Date] <= YT.ServiceEndDate
                                OR  YT.ServiceEndDate IS NULL)
GROUP BY D.[Date]
ORDER BY D.[Date];
GO

DROP TABLE dbo.YourTable;

Note that then results don't reflect your expected results, I suspect your expected results are wrong. For example you have 2 people live on 2022-01-01, however, there is only 1 person who is live on that date: Tom.

This solution will also never have Mark as "live" (the rCTE method in the other answer won't either) as their end date is before their Live date. If someone can have their service end before it started, I would suggest you have a data quality issue, and you should be adding a CHECK CONSTRAINT to the table to ensure that value of ServiceEndDate is >= LiveDate.