Re: Complex Query

Can we write a PL/SQL procedure as I have to implement similar logic in displaying different kind of reports.

TIA,
Sameer

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<4023a5f2$0$28156$626a14ce_at_news.free.fr>...
> "Sameer" <sameer794_at_rediffmail.com> a écrit dans le message de
> news:2ca69ec3.0402060208.75d83c2c_at_posting.google.com...
> > Hi michel,
> >
> > Thanks for the reply. AS I am a newbie in oracle, at first the code
> > looked a bit confusing. Can you pls. explain a little about the SQL.
> >
> > TIA
> > Sameer
> > "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,
> > > >
> > > > I am facing a problem in writing a query/PL-SQL procedure for the
> > > > following. We have a requirement to list the month-wise count of
> > > > resolved and pending fraud cases for the last 6 months. Our table
> > > > contains the columns case_id, start_date, end_date along with some
> > > > other columns and the business rules as are as follows.
> > > >
> > > > If a case has been opened and resolved in the same month, then it is
> > > > counted as a resolved case, otherwise it is 'pending' for the month in
> > > > which it has been started and the following months, until it is
> > > > 'resolved' in the month in which it has been closed.
> > > >
> > > > For ex, Consider the following.
> > > > 1. case1 has been opened on 1Apr2003 and closed on 2May2003
> > > > 2. case2 has been opened on 15Apr2003 and closed on 20Apr2003
> > > > 3. case3 has been opened on 1May2003 and closed on 3Aug2003.
> > > >
> > > > So when I run a query in Oct2003, result should be as below:
> > > >
> > > > Apr May Jun July Aug Sept
> > > > Resolved 1(case2) 1(case1) 0 0 1 0
> > > > Pending 1(case1) 1(case3) 1(case3) 1(case3) 0 0
> > > >
> > > > we need to show the count only. the case_id has been mentioned just
> > > > for understanding.
> > > >
> > > > Hope I sound clear;-)
> > > > reply asap
> > > >
> > > > TIA,
> > > > Sameer
> > >
> > >
> > > Just for fun:
> > >
> > > 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.
> > >
> > > Regards
> > > Michel Cadot
>
> Hi Sameer,
>
> As Daniel said it's just not maintainable and i don't encourage you
> to use this query (or the other i posted). It was just for fun.
>
> For these reasons, i'm sorry but i don't explain it.
> If you can't understand it then you can't maintain it.
>
> Regards
> Michel Cadot
Received on Sat Feb 07 2004 - 00:07:31 CST