Solved

query to determine year and months

Posted on 2013-02-06
5
192 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:Scott Pletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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