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:

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: