Group by Aged Days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by Aged Days

Author

Topic

gtFlue
Starting Member

8 Posts

Posted - 2012-05-10 : 13:56:42

Afternoon all.

This is a bit of an odd one for me, not sure if it can be done.

In my query I'm selecting ServiceNumber, ServiceDate and TotalBilled. I only need a count of ServiceNumbers by ServiceDate and Sum of TotalBilled.

What I am looking at doing is converting the ServiceDate into '0-30', '31-60', '61-90', etc.

The results would look something like;

ServiceNumber | TotalBilled | ServiceAge
15 150.22 0-30
30 1520.02 31-60
2 75.00 61-90
etc.....

Is something like this possible with a standard query, or will I need to incorporate variables and temp tables to pull this off?

Thanks much.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 14:44:27

Do you know how to calculate the Service Age in days, or do you need help with that too?

Assuming you have the # of days old for each service, you can join to a virtual table that contains the ranges:



;with ServiceRanges
as
(
	select '0-30' as Range, 0 as Min, 30 as Max union all
	select '31-60', 31, 60 union all
	select '61-90', 61, 90 union all
	select '91+', 91, 99999
)
select 
	sr.Range, SUM(YourData.Billed) as TotalBilled, etc ...
from
  ServiceRanges  sr
inner join
	YourData on YourData.Days between sr.Min and sr.Max 
group by
	sr.Min, sr.Range
order by
	sr.Min

The "ServiceRanges" common table expression in the beginning is where you can define your groupings and ranges. Just select from THAT, and join to your data, and you should be able to get what you need. You can also considering storing your ranges in a permanent table if you need to group on these ranges in multiple queries, and for best performance in the long-term.

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 14:48:25

is the grouping 0-30,31-60 etc based on age from current date? if yes you can do like


SELECT COUNT(ServiceNumber) AS ServiceNumber,
SUM(TotalBilled) AS TotalBilled,
ServiceAge
FROM
(
SELECT CASE 
       WHEN DATEDIFF(dd,ServiceDate,GETDATE()) <=30 THEN '0-30'
       WHEN DATEDIFF(dd,ServiceDate,GETDATE()) BETWEEN 31 AND 60 THEN '31-60'
       WHEN DATEDIFF(dd,ServiceDate,GETDATE()) BETWEEN 61 AND 90  THEN '61-90'
       ELSE 'Over 90'
       END AS ServiceAge,TotalBilled,ServiceNumber
FROM table
)t
GROUP BY ServiceAge

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gtFlue
Starting Member

8 Posts

Posted - 2012-05-11 : 11:28:36

Thanks for that. I'll give both versions a try and see which one works best in my situation. I had thought about select Case but was unable to find any examples for something similar to what I was wanting to accomplish.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 14:53:34

welcome
let us know how you got on
if you want to make it scalable i would definitely like to suggest maintaining range values in a table with descriptions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources