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_Months INT = 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.
No need for recursion.
You can work set based:
DECLARE @Last_N_Months tinyint = 24
, @Include_Current_Month bit = 1;
;with Differ(Increment) as(
select top(@Last_N_Months) ROW_NUMBER() over (order by @@spid) – @Include_Current_Month
from master..spt_values
)
select EOMONTH(dateadd(MM, -1 * Increment, current_timestamp)) as MonthEnd
, @Last_N_Months – Increment + 1 – @Include_Current_Month as MonthID
from Differ;
Thanks for posting the alternative solution. I’ll surely try it.