I'm having trouble writing a recursive function that would count the number of active clients on any given day.

Say I have a table like this:

I want to return a table that would look like this:

Is there a way to do this? Ideally, I'd have a fixed start date and go to today's date.

Some pieces I have tried:

Creating a recursive date table

Truncated to Feb 1, 2022 for simplicity:

WITH DateDiffs AS (
    SELECT DATEDIFF(DAY, '2022-02-02', GETDATE()) AS NumDays
)

, Numbers(Numbers) AS (
    SELECT MAX(NumDays) FROM DateDiffs
    UNION ALL
    SELECT Numbers-1 FROM Numbers WHERE Numbers > 0 
    ) 

, Dates AS (
    SELECT
        Numbers
        , DATEADD(DAY, -Numbers, CAST(GETDATE() -1 AS DATE)) AS [Date]
    FROM Numbers
)

I would like to be able to loop over the dates in that table, such as by modifying the query below for each date, such as by @loopdate. Then UNION ALL it to a larger final query. I'm now stuck as to how I can run the query to count the number of active users:

SELECT
COUNT(Client)
FROM clients
WHERE [Start Date] >= @loopdate AND ([End Date] <= @loopdate OR [End Date] IS NULL)

Thank you!

CodePudding user response：

You don't need anything recursive in this particular case, you need as a minimum a list of dates in the range you want to report on, ideally a permanent calendar table.

for purposes of demonstration you can create something on the fly, and use it like so, with the list of dates something you outer join to:

with dates as (
    select top(9) 
      Convert(date,DateAdd(day, -1   Row_Number() over(order by (select null)), '20220101')) dt
    from master.dbo.spt_values
)

select d.dt [Date], c.NumActive
from dates d
outer apply (
  select Count(*) NumActive
  from t
  where d.dt >= t.StartDate and (d.dt <= t.EndDate or t.EndDate is null)
)c

See this Demo Fiddle