Solved

query to determine year and months

Posted on 2013-02-06
5
190 Views
Last Modified: 2013-02-08
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
Comment
Question by:fwstealer
  • 2
  • 2
5 Comments
 

Author Comment

by:fwstealer
ID: 38861818
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38861822
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
 

Author Comment

by:fwstealer
ID: 38861849
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 38862379
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
 
LVL 48

Expert Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now