Re: Complex Query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 8 Feb 2004 09:29:24 +0100
Message-ID: <4025f2de$0$28139$636a15ce@news.free.fr>

"Sameer" <sameer794_at_rediffmail.com> a écrit dans le message de news:2ca69ec3.0402062207.6e28c48d_at_posting.google.com...
> Can we write a PL/SQL procedure as I have to implement similar logic
> in displaying different kind of reports.
>
> TIA,
> Sameer
>

Yes you can do something like the following:

SQL> set serveroutput on
SQL> def cur_date='10/15/2003'
SQL> declare
  2   type counter_rec is record (
  3      month    varchar2(30),
  4      pending  binary_integer,
  5      resolved binary_integer
  6      );
  7   type counter_tab is table of counter_rec
  8      index by binary_integer;

  9 counters counter_tab;

 10   yc binary_integer;  /* Current year, we keep only counters for this year */
 11   mc binary_integer;  /* Current month */
 12   ys binary_integer;  /* Start year    */
 13   ms binary_integer;  /* Start month   */
 14   ye binary_integer;  /* End year      */
 15   me binary_integer;  /* End month     */
 16   mf binary_integer;  /* First month of pending */

 17 begin
 18 /* Initialisations */
 19 yc := to_number(to_char(to_date('&cur_date','MM/DD/YYYY'),'YYYY'));  20 mc := to_number(to_char(to_date('&cur_date','MM/DD/YYYY'),'MM'));  21 for i in 1..mc loop

 22      counters(i).month := to_char(to_date(i,'MM'),'Mon')||' '||yc;
 23      counters(i).pending := 0;
 24      counters(i).resolved := 0;

 25 end loop;
 26 /* Loop on records of test */
 27 for rec in (select start_date, end_date from test) loop

 28      ms := to_number(to_char(rec.start_date,'MM'));
 29      ys := to_number(to_char(rec.start_date,'YYYY'));
 30      me := to_number(to_char(rec.end_date,'MM'));
 31      ye  := to_number(to_char(rec.end_date,'YYYY'));
 32      /* Check if resolved during the year in a month before current date */
 33      if ( rec.end_date is not null and ye = yc and me < mc ) then
 34         /* Increment counter */
 35         counters(me).resolved := counters(me).resolved + 1;
 36      end if;
 37      /* Check if pending in a month of current year */
 38      if ( rec.end_date is null /* Not resolved or... */
 39         or ( ye = yc and me > 1 /* ...resolved during the year and... */
 40     and ( ye != ys or me != ms ) /* ...not resolved in the starting month */
 41     )
 42         ) then
 43         /* Calculate first month of pending */
 44         if ( ye != ys ) then mf := 1; else mf := ms; end if;
 45         /* Increment counters for month before resolved */
 46         for i in ms..me-1 loop
 47    counters(i).pending := counters(i).pending + 1;
 48         end loop;
 49      end if;

 50 end loop;
 51 /* Display result */
 52 dbms_output.put_line ('Month Resolved Pending');  53 for i in 1..mc-1 loop

 54     dbms_output.put_line (counters(i).month||' '||
 55      to_char(counters(i).resolved, '999G999')||' '||
 56      to_char(counters(i).pending, '999G999'));

 57 end loop;
 58 end;
 59 /
Month Resolved Pending

Jan 2003        0        0
Feb 2003        0        0
Mar 2003        0        0
Apr 2003        1        1
May 2003        1        1
Jun 2003        0        1
Jul 2003        0        1
Aug 2003        1        0
Sep 2003        0        0

PL/SQL procedure successfully completed.

Sure this code is not optimal but you can make your own from that.

Regards
Michel Cadot Received on Sun Feb 08 2004 - 02:29:24 CST