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.