Here is a simple T-SQL script that may come handy if you need the month end dates of last N months. N is the number of months.
/*
Assign the dynamic number of months here.
You can write a procedure / table-valued function to accept this value as a parameter.
*/DECLARE @Last_N_MonthsINT=24
; WITH cte_last_N_months
AS
(
SELECT DATEADD(MM, (@Last_N_Months * -1), GETDATE()) AS [Date]
UNION ALL
SELECT DATEADD(MM, 1, [Date]) AS [Date]
FROM cte_last_N_months
WHERE [Date] < DATEADD(MM, -1, CAST(GETDATE() AS DATE))
)
SELECT EOMONTH([Date]) AS [Date]
, ROW_NUMBER() OVER(ORDER BY [Date] ASC) AS MonthID
FROM cte_last_N_months
ORDER BY 1 ASC
OPTION (MAXRECURSION 0);
The output will look like as can be seen in the image below.
