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
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
Jan 2016 - 32
Feb 2016 - 33
Mar 2016 - 33
One method generates for the 60 months and use that in the
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.