Solved

# MSSQL Frequency of Years From Days Field

Posted on 2016-11-29
35 Views
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
Question by:ttist25

LVL 28

Accepted Solution

Pawan Kumar earned 500 total points
ID: 41905758
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

--
``````

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

Hope it helps !!
0

LVL 1

Author Closing Comment

ID: 41905850
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

Question has a verified solution.

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