Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

query to determine year and months

Posted on 2013-02-06
5
Medium Priority
?
196 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 70

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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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