Solved

# Grouping dates

Posted on 2005-04-25
203 Views
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
Question by:sagarh

LVL 44

Accepted Solution

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

LVL 14

Expert Comment

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

LVL 14

Expert Comment

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

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.