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
Medium Priority
196 Views
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
``````
etc...
0
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
• 2
• 2

Author Comment

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);
``````

results in
mnths
1
2
3
4
5
etc to 121
0

LVL 70

Expert Comment

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
FROM cteTally
WHERE
num BETWEEN 0 AND DATEDIFF(MONTH, '20030101', GETDATE()) / 3 + 1
ORDER BY
num DESC
0

Author Comment

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);
``````

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

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)
``````
`````` 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
``````
0

LVL 49

Expert Comment

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

## Featured Post

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.
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…
###### Suggested Courses
Course of the Month10 days, 13 hours left to enroll