MSSQL Frequency of Years From Days Field

Good morning!

I have a table with a field of elapsed days between two points.  I'm trying to do a frequency of rows converting days into years.  

For example, given the following data:

Days |
--------
200   |
--------
100   |
--------
860   |
--------
368   |
--------
479   |
--------

The result would look something like:
<1 year = 2
1 - 2 years = 2
2 - 3 years = 1

I want to group the frequencies as follows:
< 1 year
1 - 2 years
2 - 3 years
3 - 4 years
4 -5 years
> 5 years

I'm thinking some kind of case statement will get me there but I'm not sure how to combine it with a group by (if that's even possible).  

Thanks in advance for any help.
LVL 1
ttist25Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
try..


--


CREATE TABLE grouper
(
	Days INT
)
GO

INSERT INTO grouper VALUES (200),(100),(860),(368),(479),(6000),(88000)
GO

SELECT      SUM(CASE WHEN days <= 365 THEN 1 ELSE 0 END) '<1Year'		
		 ,	SUM(CASE WHEN days > 365 AND days <= 365*2 THEN 1 ELSE 0 END) '1 - 2 Year'		
		 ,	SUM(CASE WHEN days > 365*2 AND days <= 365*3 THEN 1 ELSE 0 END) '2 - 3 Year'		
		 ,	SUM(CASE WHEN days > 365*3 AND days <= 365*4 THEN 1 ELSE 0 END) '3 - 4 Year'		
		 ,	SUM(CASE WHEN days > 365*4 AND days <= 365*5 THEN 1 ELSE 0 END) '4 - 5 Year'	
		 ,  SUM(CASE WHEN days <= 365 THEN 1 ELSE 0 END) ' > 5Years'			 
FROM grouper

--

Open in new window



Output

<1Year      1 - 2 Year  2 - 3 Year  3 - 4 Year  4 - 5 Year   > 5Years
----------- ----------- ----------- ----------- ----------- -----------
2           2           1           0           0           2

(1 row(s) affected)

Open in new window


Hope it helps !!
0
 
ttist25Author Commented:
Thanks Pawan!  

That worked great.  I modified the last line to read:
SUM(CASE WHEN days > 365*5 THEN 1 ELSE 0 END) ' > 5 Years'

But that's exactly what I needed.  

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.