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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.