• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

query to determine year and months

how would i get a query to determine the following - a list of months in increments of 3 from current yr to 2003?

like:

--months years  name
--3				3 mnths
--6				6 mnths
--9				9 mnths
--12	1		1 yr
--15	
--18
--24	2
--27
--30
--33
--36	3

Open in new window

etc...
0
fwstealer
Asked:
fwstealer
  • 2
  • 2
1 Solution
 
fwstealerAuthor Commented:
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);

Open in new window


results in
mnths
1
2
3
4
5
etc to 121
0
 
Scott PletcherSenior DBACommented:
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
0
 
fwstealerAuthor Commented:
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);

Open 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
0
 
PortletPaulCommented:
leaving (v.nice!) suggested CTE for all months, but using modulo to filter out unwanted months (provides some flexibility)
with CTE as (
	select 1 as mnths
	union all 
	select mnths + 1 from CTE 
	where mnths <= (select (year(getdate()) - 2003) * 12)
)
select
  cte.mnths
, case when cte.mnths % 12 = 0 then cte.mnths / 12 end as yrs 
, case when cte.mnths % 12 = 0 then year(getdate()) - (cte.mnths / 12) end as yyyy 
, case 
   when cte.mnths = 12     then cast((cte.mnths / 12) as varchar) + ' Year'
   when cte.mnths % 12 = 0 then cast((cte.mnths / 12) as varchar) + ' Years'
   when cte.mnths / 12 = 0 then cast(cte.mnths % 12 as varchar) + ' Months'
   when cte.mnths / 12 = 1 then '1 Year ' + cast(cte.mnths % 12 as varchar) + ' Months'
   else cast((cte.mnths / 12) as varchar) + ' Years ' + cast(cte.mnths % 12 as varchar) + ' Months'
  end as name 
from CTE
where mnths % 3 = 0
option (maxrecursion 0)

Open in new window

 mnths     yrs     yyyy     name             
 --------  ------  -------  ---------------- 
 3                          3 Months         
 6                          6 Months         
 9                          9 Months         
 12        1       2012     1 Year           
 15                         1 Year 3 Months  
 18                         1 Year 6 Months  
 21                         1 Year 9 Months  
 24        2       2011     2 Years          
 27                         2 Years 3 Months 
 30                         2 Years 6 Months 
 33                         2 Years 9 Months 
 36        3       2010     3 Years          
 39                         3 Years 3 Months 
 42                         3 Years 6 Months 
 45                         3 Years 9 Months 
 48        4       2009     4 Years          
 51                         4 Years 3 Months 
 54                         4 Years 6 Months 
 57                         4 Years 9 Months 
 60        5       2008     5 Years          
 63                         5 Years 3 Months 
 66                         5 Years 6 Months 
 69                         5 Years 9 Months 
 72        6       2007     6 Years          
 75                         6 Years 3 Months 
 78                         6 Years 6 Months 
 81                         6 Years 9 Months 
 84        7       2006     7 Years          
 87                         7 Years 3 Months 
 90                         7 Years 6 Months 
 93                         7 Years 9 Months 
 96        8       2005     8 Years          
 99                         8 Years 3 Months 
 102                        8 Years 6 Months 
 105                        8 Years 9 Months 
 108       9       2004     9 Years          
 111                        9 Years 3 Months 
 114                        9 Years 6 Months 
 117                        9 Years 9 Months 
 120       10      2003     10 Years         

Open in new window

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now