asked on

Grouping dates

I have a query which returns the number of days a log is open but I want to go further with this and have a summary that shows logs open more than 120 days / 90 days / 60 days / 30 days and less than 30 days any ideas?

I am using - Select DateDiff(day,Createdate,GetDate()) As NumberOfDays From logs - to calculate the number of days which works fine can I somehow now get this to just show the 5 categories mentioned?

Thanks

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Renante Entera

Hi sagarh!

You can simply do a UNION. Something like this :

Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, 'Less than 30 days' As Category
Where DateDiff(day,Createdate,GetDate()) < 30
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '30 days' As Category
Where DateDiff(day,Createdate,GetDate()) = 30
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '60 days' As Category
Where DateDiff(day,Createdate,GetDate()) = 60
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '90 days' As Category
Where DateDiff(day,Createdate,GetDate()) = 90
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, 'More than 120 days' As Category
Where DateDiff(day,Createdate,GetDate()) > 120
From logs

Hope this helps you. Just try it.

Goodluck!
eNTRANCE2002 :-)

Renante Entera

Oooopsss... ! Just a revision.

:: REVISED QUERY ::

Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, 'Less than 30 days' As Category
Where DateDiff(day,Createdate,GetDate()) < 30
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '30 days' As Category
Where DateDiff(day,Createdate,GetDate()) Between 30 And 59
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '60 days' As Category
Where DateDiff(day,Createdate,GetDate()) Between 60 And 89
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, '90 days' As Category
Where DateDiff(day,Createdate,GetDate()) Between 90 And 120
From logs
UNION ALL
Select DateDiff(day,Createdate,GetDate()) As NumberOfDays, 'More than 120 days' As Category
Where DateDiff(day,Createdate,GetDate()) > 120
From logs

Regards!
eNTRANCE2002 :-)