Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

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.
0
ttist25
Asked:
ttist25
1 Solution
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now