Re: Complex Query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 5 Feb 2004 20:46:41 +0100
Message-ID: <40229d6b$0$294$636a15ce@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 Received on Thu Feb 05 2004 - 13:46:41 CST