Monthly headcount with leavers

Question

I'm trying to do something similar to this thread How to get number of employees per year

The difference is that I need the headcount by month and also compute the number of leavers.

I have slightly modified the query so that instead of [year], I get [year-month]. So I convert the date into a string and extract the first 6 characters (YYYYMM) and convert it to an integer.

DECLARE @FromYear int, @ToYear int

SELECT @FromYear = 201801,
       @ToYear = convert(int,LEFT(CONVERT(char,GETDATE(),112),6))
FROM [Airconnect].[dbo].[CoreNZ]

;WITH CTE AS 
(
    SELECT @FromYear As TheYear
    UNION ALL
    SELECT TheYear + 1
    FROM CTE
    WHERE TheYear < @ToYear
)

SELECT TheYear as [Year], 
       COUNT
       (
       CASE WHEN TheYear <= convert(int,LEFT(CONVERT(char,COALESCE([Staff_End_Date], GETDATE()),112),6)) THEN 
           1 
       END
       ) As [No. of Employees.]
FROM CTE
INNER JOIN [dbo].[CoreNZ] ON(TheYear >= convert(int,LEFT(CONVERT(char,Start_Date,112),6)))
GROUP BY TheYear

I get the following message:

Msg 530, Level 16, State 1, Line 8 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Provide sample data, desired results, and an explanation in *your* question of what you want to accomplish. — Gordon Linoff, Jun 27 '19 at 22:53
you are missing a close parenthesis in "@FromYear = convert(int,(LEFT(CONVERT(char,MIN([Start_Date])),6))," ->> There are 5 open "(" characters, and only 4 ")" close... — SQLRaptor, Jun 27 '19 at 22:56
Thanks @SQLRaptor ! I've added the parenthesis but I get the following error message: Conversion failed when converting the varchar value 'Jun 28' to data type int. I thought the date format in SQL was YYYYMMDD... Doesn't seem to be the case... — Negarev, Jun 27 '19 at 23:17
Can you please confirm the [Start_Date] is a datetime or other date type? Since you want dates at the end, I would recommend you do something like this, which I've had success with in the past: Set all dates within a month to the first or last day of the month, e.g. all June 2019 dates are converted to 20190601... and I'll show you how: DATEFROMPARTS(YEAR([Start_Date]),MONTH([Start_Date]),1) — mgrollins, Jun 27 '19 at 23:40
Why don't you just use the Year(date) and Month(date) functions from sql? Forget about all that string parsing and conversion. — Programnik, Jun 27 '19 at 23:46
`convert(int,LEFT(CONVERT(char,GETDATE(),112),6))`. You need to convert to `yyyyMM` first. `Style` parameter is **112**. — Alex Kudryashev, Jun 28 '19 at 00:36
Thanks @AlexKudryashev! I can get past the scalar setup phase with that, but still get an error message : Msg 530, Level 16, State 1, Line 8 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. — Negarev, Jun 28 '19 at 04:13
possibly duplicate of this https://stackoverflow.com/questions/41183822/sql-server-the-maximum-recursion-100-has-been-exhausted-before-statement-compl — kiran gadhe, Jun 28 '19 at 04:58

Serkan Arslan · Answer 1 · 2019-06-28T06:57:31.703

Because your year recursion exceeds the limit of recursion CTE limits(default is 100). You can change this limit by adding MAXRECURSION option.

OPTION (MAXRECURSION 500)

you can add this option end of your query.

DECLARE @FromYear int, @ToYear int

SELECT @FromYear = 201801,
       @ToYear = convert(int,LEFT(CONVERT(char,GETDATE(),112),6))
FROM [Airconnect].[dbo].[CoreNZ]

;WITH CTE AS 
(
    SELECT @FromYear As TheYear
    UNION ALL
    SELECT TheYear + 1
    FROM CTE
    WHERE TheYear < @ToYear
)

SELECT TheYear as [Year], 
       COUNT
       (
       CASE WHEN TheYear <= convert(int,LEFT(CONVERT(char,COALESCE([Staff_End_Date], GETDATE()),112),6)) THEN 
           1 
       END
       ) As [No. of Employees.]
FROM CTE
INNER JOIN [dbo].[CoreNZ] ON(TheYear >= convert(int,LEFT(CONVERT(char,Start_Date,112),6)))
GROUP BY TheYear
OPTION (MAXRECURSION 500)

An additional, if you only need year and months, this version of query much better I think.

DECLARE @FromYear DATE, @ToYear DATE

SELECT @FromYear = '20180101',
       @ToYear = CAST(GETDATE() AS DATE)
FROM [Airconnect].[dbo].[CoreNZ]

;WITH CTE AS 
(
    SELECT @FromYear As TheYear
    UNION ALL
    SELECT DATEADD(MONTH,1,TheYear)
    FROM CTE
    WHERE TheYear < @ToYear
)

SELECT TheYear as [Year], 
       COUNT
       (
       CASE WHEN TheYear <= COALESCE([Staff_End_Date], GETDATE()) THEN 
           1 
       END
       ) As [No. of Employees.]
FROM CTE
INNER JOIN [dbo].[CoreNZ] ON(TheYear >= Start_Date)
GROUP BY TheYear

Monthly headcount with leavers

1 Answers1