query to determine year and months

wait this needs to be in increments of 12 months from current yr to 2003.

declare @beginYear as varchar(10)
declare @currentYear as date
declare @diff as int
declare @mnths as int
set @beginYear = '2003'
set @currentYear = getdate() --2013
set @diff = cast(Year(@currentYear) as int) - @beginYear --10
set @mnths = @diff * 12 --120
;with CTE as (
	select 1 as mnths
	union all 
	select mnths + 1 from CTE 
	where mnths <= @mnths--120
)
select * from CTE
option (maxrecursion 0);

Select allOpen in new window

results in
mnths
1
2
3
4
5
etc to 121

I'm not 100% sure of what you need, but something like this should get you (at least) very close (hopefully!):


;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT
    DATEADD(MONTH, num * 3, '20030101')
FROM cteTally
WHERE
    num BETWEEN 0 AND DATEDIFF(MONTH, '20030101', GETDATE()) / 3 + 1
ORDER BY
    num DESC

scott - think if i can figure out how to get the following to return in increments of 12 months that will work:

declare @beginYear as varchar(10)
declare @currentYear as date
declare @diff as int
declare @mnths as int
set @beginYear = '2003'
set @currentYear = getdate() --2013
set @diff = cast(Year(@currentYear) as int) - @beginYear --10
set @mnths = @diff * 12 --120
;with CTE as (
	select 1 as mnths
	union all 
	select mnths + 1 from CTE 
	where mnths <= @mnths--120
)
select * from CTE
option (maxrecursion 0);

Select allOpen in new window

12
24
36
etc..

cause 2013 - 2003 is 10 yrs
of course the variable for 2013 is the currentyear and 2003 is static

Hi, have we solved your query for months/years, by quarter, back to 2003?