Here is a simple T-SQL script that may come handy if you need the month end dates of last N years. N is the number of years.
/*
Assign the dynamic number of years here.
You can write a procedure / table-valued function to accept this value as a parameter.
*/
DECLARE @Last_N_Years INT = 2
; WITH cte_last_N_years
AS
(
SELECT DATEADD(YY, (@Last_N_Years * -1), GETDATE()) AS [Date]
UNION ALL
SELECT DATEADD(MM, 1, [Date]) AS [Date]
FROM cte_last_N_years
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_years
ORDER BY 1 ASC
OPTION (MAXRECURSION 0);
The output will look like as can be seen in the image below.
SQL Server Carpenter 
Very nice! Here’s another way to do it without using recursion:
“`
DECLARE @Last_N_Years int = 2;
WITH c1 AS (SELECT x.x FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(x))
, c2(x) AS (SELECT 1 FROM c1 x CROSS JOIN c1 y)
, c3(x) AS (SELECT 1 FROM c2 x CROSS JOIN c2 y CROSS JOIN c2 z)
, c4(rn) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c3)
SELECT EOMONTH(DATEADD(MONTH, -x.rn,GETDATE()))
FROM c4 x
WHERE x.rn
ORDER BY x.rn DESC;
“`
This is using a method I learned from Jeff Moden (not sure where he learned it) to generate a tally table.
LikeLiked by 1 person
Weird, the WHERE clause got stripped a bit. Must have gotten cleaned out to remove brackets.
I put it here instead:
https://gist.github.com/chadbaldwin/b7b9feb082beb6e72dd5f00a3df37d2c
LikeLiked by 1 person
I used a simple loop, without any tally table and got the same. Compared with CTE, the cost is 37% vs 63% with CTE. And the end date can be changed.
declare @year int = 2
declare @endDate date = getdate()
declare @startDate date = dateadd(month, -@year * 12, @endDate)
declare @month table (EOM date)
declare @current int = 0
while (@current < @year * 12)
begin
insert into @month
values(EOMONTH(dateadd(month, @current, @startDate)))
set @current += 1
end
select * from @month
LikeLike
Using either a Recursive CTE or a While loop is resource intensive method even for supposed “small” rowcounts. The only way to break the habit is to stop practicing the habit just because you have a small row count.
LikeLike