Re: Complex Query

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 Received on Thu Feb 05 2004 - 19:31:34 CST