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

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
Asked:
sagarh
  • 2
1 Solution
 
TimCotteeCommented:
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
 
Renante EnteraCommented:
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
 
Renante EnteraCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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