querying from date fields

[rboscia]

I am trying to query a table and find tickets that are
open between 5 -30 days, 31-60 days & 61-90
days. I want all 3 queries in one report and I
would like them totaled at the bottom.

My query so far is
select
tabletwo.ticket "Ticket ",
tabletwo.datecreated "Date "

from tableone, tabletwo
where tableone.status = 'Open'
and trunc(tabletwo.datecreated) <= trunc
(sysdate-5)
and trunc (tabletwo.datecreated) >= trunc (sysdate
-30)

My question is (and this may be easy - but I
am a newbie & I'm not even 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:

Ticket Date
3241 05/31/2002

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

 

[petersJazz]

It looks to me that what you want is to run three different queries. So just make three queries as a script ant run. Exact how to run will differ in different RDBMS.

 

[angiole]

What DB are you using?

 

[rboscia]

I am using Oracle
I did try using UNION which gives me the results I need,
but then I'm not sure how to get each one of the queries grouped , so my output shows
Tickets Open 5-30 days
xx xxx xxxx xxx

Tickets Open 31-60 days
xx xxx xxxx xxx

etc.,
I DO NOT know scripting, so I am sort of &quot;lost in space&quot;....

 

[angiole]

Oracle v.8.1.x comes built in with many analytical SQL functions.

follow this link for further info ...

http://technet.oracle.com/docs/prod.../817_doc/server.817/a76994/analysis.htm#11237

In summary, you will do the following
select
(case when date < 30 then '<30' else
when date < 60 then '<60' else '>=60' end) as date_group,
count/sum/avg(...)
from table_1
where ...
group by
(case when date < 30 then '<30' else
when date < 60 then '<60' else '>=60' end)

You can get clever here and replace 30 + 60 days with a prompted value which would increase the functionality of the query result.

 

[angiole]

As well, forgot to add that the following will generate a subtotal for each grouped value

select
(case when date < 30 then '<30' else
when date < 60 then '<60' else '>=60' end) as date_group,
count/sum/avg(...)
from table_1
where ...
group by rollup (
(case when date < 30 then '<30' else
when date < 60 then '<60' else '>=60' end))

 

[petersJazz]

If you choose to have a union you should use &quot;union all&quot; and you can add a textfield for sorting, like:

select ..., 'Q1' sortit
from ...
union all
select ..., 'Q2'
from ...
union all
select ..., 'Q3'
from ...
where ...
order by sortit