Is there a way to improve this query showing a monthly timeline of the number of active employees?

Question

I have a Microsoft SQL database with all past and present employees with their start and leave date. I would like to know how many employees were active each month in the past years. While I did find a solution, I was wondering if there is a better one. I eventually want to create a dashboard with not just this one timeline but lots of similar ones and always aggregating the data in the way I did seems too complex.

Here is an example table similar to the one I want to use:

employee_id	first_name	last_name	start_date	leave_date
1	Example	User	2022-12-28	2023-05-30
2	Second	Example	2023-01-14	2023-12-31
3	Third	Try	2023-02-05	2024-04-15

This is the script I wrote. It gives the correct results I think, however, it seems quite complicated to me for something I thought was a pretty straight forward requirement.

DECLARE @startDate date = '2022-11-01';

CREATE TABLE #temp (
    month_ date,
    nb_employees int
)

WHILE @startDate < CURRENT_TIMESTAMP
BEGIN
    INSERT INTO #temp
        SELECT @startDate, COUNT(employee_id)
        FROM employee_example
        WHERE 1=1
            AND (YEAR(start_date) < YEAR(@startDate)
            OR (YEAR(start_date) = YEAR(@startDate) AND MONTH(start_date) <= MONTH(@startDate)))
            AND (YEAR(leave_date) > YEAR(@startDate)
            OR (YEAR(leave_date) = YEAR(@startDate) AND MONTH(leave_date) >= MONTH(@startDate)))


    SET @startDate = DATEADD(month,1,@startDate)
END

SELECT * FROM #temp

DROP TABLE #temp

The result looks like this:

month_	nb_employees
2022-11-01	0
2022-12-01	1
2023-01-01	2
2023-02-01	3
2023-03-01	3
2023-04-01	3
2023-05-01	3
2023-06-01	2
...	...

Answer 1

As I mentioned in the comments, use a Calendar table; this makes it way easier. You can then just LEFT JOIN to your employee's table to get the count of users:

DECLARE @StartDate date = '20221101'; --Assumed this will always be the first of the month

SELECT C.CalendarDate AS Month,
       COUNT(ee.employee_id) AS Employees
FROM tbl.Calendar C
     LEFT JOIN dbo.employee_example ee ON C.CalendarDate >= ee.start_date
                                      AND C.CalendarDate < ee.leave_date
WHERE C.CalendarDate >= @StartDate
  AND C.CalendarDate < SYSDATETIME()
  AND C.CalendarDay = 1
GROUP BY C.CalendarDate;

If you don't have a Calendar table, create one. If you are really adverse to creating one (please do create one, they have so many uses!) then you can use a tally:

DECLARE @StartDate date = '20221101';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP(DATEDIFF(MONTH,@StartDate, SYSDATETIME()))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1,000 months is more than enough
Months AS(
    SELECT DATEADD(MONTH, T.I, @StartDate) AS Month
    FROM Tally T)
SELECT M.Month,
       COUNT(ee.employee_id) AS Employees
FROM Months M
     LEFT JOIN dbo.employee_example ee ON M.Month >= ee.start_date
                                      AND M.Month < ee.leave_date
GROUP BY M.Month;

If you're on 2022+ you can skip a couple steps and use GENERATE_SERIES:

DECLARE @StartDate date = '20221101';

WITH Months AS(
    SELECT DATEADD(MONTH, GS.value, @StartDate) AS Month
    FROM GENERATE_SERIES(0,DATEDIFF(MONTH,@StartDate, SYSDATETIME())) GS)
SELECT M.Month,
       COUNT(ee.employee_id) AS Employees
FROM Months M
     LEFT JOIN dbo.employee_example ee ON M.Month >= ee.start_date
                                      AND M.Month < ee.leave_date
GROUP BY M.Month;