SQL Active Users by day

Question

I've got a table with CustomerID, StartDate and EndDate.
I'm trying to create a table with the following columns: Date, ActiveUsers.

The Date needs to be all dates between 01/01/2016 and today. ActiveUsers is a count of CustomerID where the Date falls between the StartDate and EndDate.

I hope all that makes sense.

I found code that gives me a list of dates but I have no idea how I can join my customers table to this result.

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE

SET @StartDateTime = '2016-01-01'
SET @EndDateTime = GETDATE();

WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData <= @EndDateTime
)
SELECT dr.DateData
FROM DateRange dr
OPTION (MAXRECURSION 0) 
GO

Zohar Peled · Accepted Answer · 2019-03-08T09:16:10.213

This is a simple left join, group by and count:

SELECT DateData, COUNT(CustomerID) as ActiveUsers
FROM DateRange AS D
LEFT JOIN Customers AS C 
    ON D.DateData >= C.StartDate 
    AND D.DateData <= C.EndDate
GROUP BY DateData

However, here's a free tip: Using a recursive cte for things like that is fine when the range is small, but if you find yourself having to use OPTION (MAXRECURSION 0) it means you are in danger of a performance hit because of the recursive cte and should replace it with a tally table based solution.

If you don't know what a tally table is, read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
If you don't already have a tally table, read What is the best way to create and populate a numbers table?

Having said that, date related queries often benefit from having a pre-populated calendar table - such a table can save you from calculating weekends, national holidays etc', at a storage price that's practically negligible in modern servers. Read Aaron Bertrand's Creating a date dimension or calendar table in SQL Server for a step-by-step explanation on how to create one for yourself.

SQL Active Users by day

1 Answers1