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 ),