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

by

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

More Related Contents:

  1. SQL query for display one field only once which having multiple record
  2. T-SQL split string
  3. Exclude a column using SELECT * [except columnA] FROM tableA?
  4. Conditional WHERE clause in SQL Server
  5. How to Join to first row
  6. Concatenate row values T-SQL
  7. Multi-statement Table Valued Function vs Inline Table Valued Function
  8. How to return multiple values in one column (T-SQL)?
  9. Dynamically create columns sql
  10. Difference between CTE and SubQuery?
  11. Reference alias (calculated in SELECT) in WHERE clause
  12. When to use Common Table Expression (CTE)
  13. INT vs Unique-Identifier for ID field in database
  14. How to send email from SQL Server?
  15. How To Create Table with Identity Column
  16. Why do you create a View in a database?
  17. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  18. How to split string using delimiter char using T-SQL?
  19. Calculate the number of records for each date between 2 dates
  20. T-SQL: How to use parameters in dynamic SQL?
  21. How to compare datetime with only date in SQL Server
  22. Merge overlapping date intervals
  23. SQL server select distinct rows using most recent value only
  24. Split words with a capital letter in sql
  25. SQL poor stored procedure execution plan performance – parameter sniffing
  26. How to return a incremental group number per group in SQL
  27. Eliminate and reduce overlapping date ranges
  28. scope_identity vs ident_current
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment