All you have to set / pass in is the "@number_of_months", although you could pass in a different ending month if you wanted to.
DECLARE @number_of_months int
SET @number_of_months = 24
--------------------------------------------------------------------------------
DECLARE @start_month date
DECLARE @end_month date
--SET @end_month = '20171113'
SET @end_month = ISNULL(@end_month, GETDATE())
SET @start_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_month) - @number_of_months + 1, 0)
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT YEAR(first_day_of_month) AS year, CAST(DATENAME(MONTH, first_day_of_month) AS char(3)) AS mon,
first_day_of_month, DATEADD(DAY, -1, DATEADD(MONTH, 1, first_day_of_month)) AS last_day_of_month
FROM cteTally100 months
CROSS APPLY (
SELECT DATEADD(MONTH, months.number - 1, @start_month) AS first_day_of_month
) AS ca1
WHERE months.number <= @number_of_months
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.