Q
Problem solve Get help with specific problems with your technologies, process and projects.

Grouping rows by 5-30 days, 31-60 days, and 61-90 days

I am trying to query a table and find tickets that are open between 5-30 days, 31-60 days and 61-90 days. I want...

all three queries in one report, and I would like them totalled at the bottom.

My query so far is

select tableone.group "Assigned "
     , tabletwo.ticket "Ticket "
     , tabletwo.datecreated "Date "
  from tableone
     , tabletwo
 where tableone.status = 'Open'
   and tabletwo.assigned = tableone.person
   and trunc(tabletwo.datecreated) 
       <= trunc(sysdate-5)
   and trunc(tabletwo.datecreated) 
       >= trunc(sysdate-30)

My question is (and this may be easy but I'm not sure where to start to look) how do I get the query to pull the data for the 31-60 days and the 61-90 days?

At this point, my output shows:

Assigned  Ticket  Date 
Sales      3241  05/31/2002

I would like the report to group the tickets for 5-30 days, then group for 31-60, and then for 61-90. I've tried different things, but obviously not the right one. Thanks for any help....


I'm not sure your TRUNC technique is the right way to do date comparisons. I'm assuming this is Oracle. Doesn't TRUNC on a date field return a string? You're going to have problems when you're looking for 90-day tickets, because the standard Oracle date formats like '21-JUN-02' include month abbreviations that just don't collate properly. To say nothing about year boundaries.

When Oracle does date calculations on ordinary DATE columns, the results are always given in days, which is perfect for your requirements.

Try the following --

select case 
       when sysdate - tabletwo.datecreated
           between  5 and 30
         then ' 5-30' 
       when sysdate - tabletwo.datecreated
           between 31 and 60
         then '31-60' 
       when sysdate - tabletwo.datecreated
           between 61 and 90
         then '61-90' 
       end as agegroup
     , tableone.group "Assigned "
     , tabletwo.ticket "Ticket "
     , tabletwo.datecreated "Date "
  from tableone
     , tabletwo
 where tableone.status = 'Open'
   and tabletwo.assigned = tableone.person
   and sysdate - tabletwo.datecreated
       between 5 and 90
order by agegroup

To get totals for all, just repeat the query with 'ZZZZZ' instead of the CASE construct (so that it sorts last) and run both queries together as subselects in a UNION ALL.

For More Information


This was last published in June 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close