Steven Deam Steven Deam - 3 months ago 13
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:

SELECT
SUM(X.CountOfEmployees)
FROM (SELECT
COUNT(CNCEmployeeID) AS CountOfEmployees
FROM dbo.CNCEmployees
GROUP BY CNCEmployeeStartDate,
CNCEmployeeDateLeft
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.

Answer

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.