• Status: Solved
• Priority: Medium
• Security: Public
• Views: 297

# 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
0
sagarh
• 2
1 Solution

Commented:
Hi sagarh,

Hello again, you just need to do something like:

Select
Sum(Case When DateDiff(day,CreateDate,GetDate()) >= 120 Then 1 Else 0 End) As Open120Plus,
Sum(Case When DateDiff(day,CreateDate,GetDate()) >= 90 And DateDiff(day,CreateDate,GetDate()) < 120 Then 1 Else 0 End) As Open90Plus,
Sum(Case When DateDiff(day,CreateDate,GetDate()) >= 60 And DateDiff(day,CreateDate,GetDate()) < 90 Then 1 Else 0 End) As Open60Plus,
Sum(Case When DateDiff(day,CreateDate,GetDate()) >= 30 And DateDiff(day,CreateDate,GetDate()) < 60 Then 1 Else 0 End) As Open30Plus,
Sum(Case When DateDiff(day,CreateDate,GetDate()) < 30 Then 1 Else 0 End) As OpenLess30
From
Logs

Should give you the number of logs in each category,

Tim Cottee
0

Commented:
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 :-)
0

Commented:
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 :-)
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.