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

You can join your table to a calendar table containing all the dates you need:

WITH calendar
     AS (SELECT Cast('2022-01-01' AS DATETIME) AS d
         UNION ALL
         SELECT Dateadd(day, 1, d)
         FROM   calendar
         WHERE  d < '2022-02-01')
SELECT d        AS "Date",
       Count(*) AS NumberOfCustomers
FROM   calendar
       INNER JOIN table_name
               ON d BETWEEN livedate AND COALESCE(serviceenddate, '9999-12-31')
GROUP  BY d;

Fiddle