Solved

query to determine year and months

Posted on 2013-02-06
5
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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 49

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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