Steven Deam Steven Deam - 1 month ago 9
SQL Question

SQL Server query get count of X on 15th of each month

I would like to create an SQL query that gets the count of employees that were employed as of the 15th of each month of the last 5 years.

This query gets me a single month:

COUNT(CNCEmployeeID) AS CountOfEmployees
FROM dbo.CNCEmployees
GROUP BY CNCEmployeeStartDate,
HAVING (CNCEmployeeStartDate < CONVERT(datetime, '2016-07-15 00:00:00', 102))
AND ((CNCEmployeeDateLeft > CONVERT(datetime, '2016-07-15 00:00:00', 102))
OR (CNCEmployeeDateLeft IS NULL))) AS X

What I am looking for would output:

Jan 2016 - 32
Feb 2016 - 33
Mar 2016 - 33

etc. for each month that we have data.

I know how to create a Query and at least make it quick to change the dates by hand by adding a variable and changing that over and over (in fact I will probably do that to get the report done today for the last 12 months). I believe that there is a better way to do this in one step without the need to manually go through each month.


One method generates for the 60 months and use that in the join:

with dates as (
      select cast(dateadd(getdate(), 16 - day(getdate()), getdate()) as date) as thedate, 1 as num
      union all
      select dateadd(month, -1, thedate), num + 1
      from dates
      where num <= 60
select d.thedate, count(e.CNCEmployeeStartDate)
from dates d left join
     dbo.CNCEmployees e
     on e.CNCEmployeeStartDate <= d.thedate and
        (e.CNCEmployeeDateLeft >= d.thedate or e.CNCEmployeeDateLeft is null)
group by d.thedate;

This is not the most efficient method, but if you have a few hundred or thousand employees it should be fine in terms of performance.