How to extrapolate dates in SQL Server to calculate the daily counts?

For future questions, don’t post images of data. Instead, use a service like dbfiddle. I’ll anyhow add a sketch for an answer, with a better-prepared question you could have gotten a complete answer. Anyhow here it goes:

-- extrema is the least and the greatest date in staff table
with extrema(mn, mx) as (
    select least(min(hired),min(retired)) as mn
         , greatest(max(hired),max(retired)) as mx
    from staff
), calendar (dt) as (
    -- we construct a calendar with every date between extreme values
    select mn from extrema
    union all
    select dateadd(day, 1, d)
    from calendar
    where dt < (select mx from extrema)
-- finally we can count the number of employed people for each such date
select dt, count(1) 
from calendar c 
join staff s
    on c.dt between s.hired and s.retired
group by dt; 

If you find yourself doing this kind of calculation often, it is a good idea to create a calendar table. You can add other attributes to it such as if it is a day of in the middle of the week etc.

With a constraint as:

CHECK(hired <= retired)

the first part can be simplified to:

with extrema(mn, mx) as (
    select min(hired) as mn
         , max(retired) as mx
    from staff

Leave a Comment