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

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.


  • 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;