common table expression - SQL Server - Get All Month and Year numbers till current month from the Month and Year of joining

Question

I have employee joining date, from the date of joining to till date i would like to print Month and Years numbers.

For instance employee has joining in July-2020, i need to get data as below

MonthNumber YearNumber
    7          2020
    8          2020
    9          2020
    10         2020
    11         2020
    12         2020
     1         2021

Below is my query, i am using a CTE and trying to increment it..

DECLARE @JoiningDate Date

SET @JoiningDate = '2020-07-04 11:21:03.827'

;With MonthYears as (
SELECT monthNumber = DATEPART(m, @JoiningDate),
       yearNumber = DATEPART(YEAR, DATEADD(m, i+1, @JoiningDate),
       i = 0

UNION ALL

SELECT monthNumber = DATEPART(m, DATEADD(m, i+1, @JoiningDate)),
       yearNumber = DATEPART(YEAR, DATEADD(m, i+1, @JoiningDate)),
       i = i+1
FROM MonthYears
WHERE DATEPART(m, DATEADD(m, i+1, @JoiningDate)) <= DATEPART(m, GETDATE())
AND DATEPART(year, DATEADD(m, i+1, @JoiningDate)) <= DATEPART(year, GETDATE())
)
SELECT * FROM MonthYears

But i could see only 1 record, i.e the month and year of joining which is 7, 2020

question from:https://stackoverflow.com/questions/65641280/sql-server-get-all-month-and-year-numbers-till-current-month-from-the-month-an

Answer 1

The problem with your query is the WHERE condition

WHERE DATEPART(m, DATEADD(m, i+1, @JoiningDate)) <= DATEPART(m, GETDATE())
AND DATEPART(year, DATEADD(m, i+1, @JoiningDate)) <= DATEPART(year, GETDATE())

On the second iteration, Aug - 8 is not less than DATEPART(m, GETDATE()) = 1

I would use the first of the month date and increment by 1 month in the recursive CTE. Then use DATEPART() on the result

DECLARE @JoiningDate Date

SET @JoiningDate = '2020-07-04 11:21:03.827'

;With MonthYears as 
(
    SELECT [FirstOfMonth]   = DATEADD(MONTH, DATEDIFF(MONTH, 0, @JoiningDate), 0)

    UNION ALL

    SELECT  [FirstOfMonth]  = DATEADD(MONTH, 1, [FirstOfMonth])
    FROM    MonthYears
    WHERE   [FirstOfMonth]  < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
)
SELECT  *, 
        monthNumber = DATEPART(month, [FirstOfMonth]),
        yearNumber  = DATEPART(YEAR, [FirstOfMonth])
FROM    MonthYears