Solved

MSSQL Frequency of Years From Days Field

Posted on 2016-11-29
2
14 Views
Last Modified: 2016-11-29
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
Comment
Question by:ttist25
2 Comments
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal 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

--

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
 
LVL 1

Author Closing Comment

by:ttist25
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now