| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Complex Query
"VC" <boston103_at_hotmail.com> a écrit dans le message de news:W7CUb.185165$5V2.896782_at_attbi_s53...
> Hello Michel,
>
>
> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> news:40229d6b$0$294$636a15ce_at_news.free.fr...
> >
> > "Sameer" <sameer794_at_rediffmail.com> a écrit dans le message de
> > news:2ca69ec3.0402050544.21270566_at_posting.google.com...
> > > Hi There,
> > >
> >
> > SQL> create table test (case_id number, start_date date, end_date date);
> >
> > Table created.
> >
> > SQL> insert into test values (1, to_date('04/01/2003','MM/DD/YYYY'),
> > 2 to_date('05/02/2003','MM/DD/YYYY'));
> > SQL> insert into test values (2, to_date('04/15/2003','MM/DD/YYYY'),
> > 2 to_date('04/20/2003','MM/DD/YYYY'));
> > SQL> insert into test values (3, to_date('05/01/2003','MM/DD/YYYY'),
> > 2 to_date('08/03/2003','MM/DD/YYYY'));
> > SQL> commit;
> > SQL> select * from test;
> >
> > CASE_ID START_DATE END_DATE
> > ---------- ---------- ----------
> > 1 04/01/2003 05/02/2003
> > 2 04/15/2003 04/20/2003
> > 3 05/01/2003 08/03/2003
> >
> > 3 rows selected.
> >
> > SQL> def cur_date='10/15/2003'
> > SQL> select
> to_char(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth),'Mon YYYY')
> Month,
> > 2 count(*) nb_case,
> > 3
> sum(decode(sign(nvl(t1.end_date,to_date('12/31/2999','MM/DD/YYYY'))
> > 4 -
> trunc(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth+1),
> > 5 'MONTH')), -1,
> > 6
> decode(sign(nvl(t1.end_date,to_date('12/31/2999','MM/DD/YYYY'))
> > 7 -
> trunc(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth)-1,
> > 8 'MONTH')),
> > 9 1, 1 /* Resolved during the month */, 0 /* Resolved before
> */),
> > 10 0 /* Not resolved or resolved after */)) resolved,
> > 11 count(*)
> > 12 -
> sum(decode(sign(nvl(t1.end_date,to_date('12/31/2999','MM/DD/YYYY'))
> > 13 -
> trunc(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth+1),
> > 14 'MONTH')), -1, 1 /* Resolved */, 0 /* Pending */))
> > 15 pending
> > 16 from ( select -1*rownum nmonth
> > 17 from (Select 1 from dual group by cube(1,2,3,4))
> > 18 where rownum <
> to_number(to_char(to_date('&cur_date','MM/DD/YYYY'),'MM'))
> > 19 order by rownum desc ) cnt,
> > 20 test t1
> > 21 where trunc(t1.start_date,'MM') <
> add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth)
> > 22 group by cnt.nmonth
> > 23 /
> >
> > MONTH NB_CASE RESOLVED PENDING
> > -------- ---------- ---------- ----------
> > Apr 2003 2 1 1
> > May 2003 3 1 1
> > Jun 2003 3 0 1
> > Jul 2003 3 0 1
> > Aug 2003 3 1 0
> > Sep 2003 3 0 0
> >
> > 6 rows selected.
>
>
>
> I think it can be done in a much simpler way:
>
> select month,
> (select count(*) from test where x.month = trunc(end_date, 'mm'))
> Resolved,
> (select count(*) from test where x.month < trunc(end_date, 'mm') and
> x.month>=trunc(start_date, 'mm')) Pending
> from (select add_months(trunc(sysdate, 'mm'), -rownum-4) month from (select
> 1 from dual group by cube(1,2,3,4)) where rownum <=6) x
>
> ... or if you do not like 'cube':
>
> select month,
> (select count(*) from test where x.month = trunc(end_date, 'mm'))
> Resolved,
> (select count(*) from test where x.month < trunc(end_date, 'mm') and
> x.month>=trunc(start_date, 'mm')) Pending
> from (select add_months(trunc(sysdate, 'mm'), -rownum-4) month from
> all_objects where rownum <=6) x
>
> MONTH RESOLVED PENDING
> 9/1/2003 0 0
> 8/1/2003 1 0
> 7/1/2003 0 1
> 6/1/2003 0 1
> 5/1/2003 1 1
> 4/1/2003 1 1
>
>
> -- where 'add_months(trunc(sysdate, 'mm'), -rownum-4) ' should actually be
> 'add_months(trunc(sysdate, 'mm'), -rownum) '.
> Minus four adjusts the last month for the data supplied.
>
> Pivoting is left as an exercise for the motivated leearner ;)
>
> Rgds.
>
> VC
>
You're right your query is simpler, but i set myself some conditions: - current date is given by a define &cur_date;
so your sysdate is replaced by my to_date('&cur_date','MM/DD/YYYY')
- minimize test full table scan (1 for me, 2 for you), so the sum(decode) instead of count - query must be valid for 8i and up (not select in select clause) - query must return all rows for the current year (given by &cur_date);
so your
select add_months(trunc(sysdate, 'mm'), -rownum-4) month from (select
1 from dual group by cube(1,2,3,4)) where rownum <=6)
which depends of the current data is replaced by my
select -1*rownum nmonth
from (Select 1 from dual group by cube(1,2,3,4))
where rownum < to_number(to_char(to_date('&cur_date','MM/DD/YYYY'),'MM'))
order by rownum desc
independent of the data
But i'm agree there is certainly a simpler way than i do, even with the constraints i take. The following one is one (thanks to you):
SQL> select to_char(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth),'Mon YYYY') Month,
2 count(*) nb_case,
3 sum(decode(trunc(t1.end_date,'MONTH'),
4 trunc(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth),'MONTH'),
5 1, 0)) resolved,
6 count(*)
7 - sum(decode(sign(nvl(t1.end_date,to_date('12/31/2999','MM/DD/YYYY'))
8 - trunc(add_months(to_date('&cur_date','MM/DD/YYYY'),cnt.nmonth+1),
9 'MONTH')), -1, 1 /* Resolved */, 0 /* Pending */))
10 pending
11 from ( select -1*rownum nmonth
12 from (Select 1 from dual group by cube(1,2,3,4))
13 where rownum < to_number(to_char(to_date('&cur_date','MM/DD/YYYY'),'MM'))
14 order by rownum desc ) cnt,
15 test t1
MONTH NB_CASE RESOLVED PENDING -------- ---------- ---------- ----------
Apr 2003 2 1 1 May 2003 3 1 1 Jun 2003 3 0 1 Jul 2003 3 0 1 Aug 2003 3 1 0 Sep 2003 3 0 0
Regards
Michel Cadot
Received on Fri Feb 06 2004 - 08:31:26 CST
 |
 |