Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: Cursor / Analytics Help

    I can do this in a cursor (get those dam cursors out of my database) I want to know if this can be don with analytics.

    i need to determin the number of dat in a given year with 1 month grace and then increment the year if number of dat's for a year >= 2 resetting the number of dat's for the next year

    I have data and results below. I am tring in analytics and keep stumbling

    create table dat
    (bene varchar2(15),
    datdate date);

    insert into dat values('JOE', '1-nov-2005');
    insert into dat values('JOE', '1-jun-2006');
    insert into dat values('JOE', '1-oct-2006');
    insert into dat values('JOE', '1-dec-2006');
    insert into dat values('JOE', '1-mar-2007');
    insert into dat values('JOE', '1-nov-2007');
    insert into dat values('JOE', '1-oct-2008');
    insert into dat values('JOE', '1-dec-2008');
    insert into dat values('JOE', '1-jan-2009');
    insert into dat values('JOE', '1-jun-2009');
    commit;

    create table datresult
    (bene varchar2(15),
    datdate date,
    datcnt number,
    datyear number);

    insert into datresult values('JOE', '1-nov-2005',1,1);
    insert into datresult values('JOE', '1-jun-2006',1,2);
    insert into datresult values('JOE', '1-oct-2006',2,1);
    insert into datresult values('JOE', '1-dec-2006',2,2);
    insert into datresult values('JOE', '1-mar-2007',2,3);
    insert into datresult values('JOE', '1-nov-2007',3,1);
    insert into datresult values('JOE', '1-oct-2007',3,2);
    insert into datresult values('JOE', '1-dec-2008',4,1);
    insert into datresult values('JOE', '1-jan-2009',4,2);
    insert into datresult values('JOE', '1-jun-2009',4,3);

    commit;
    Last edited by rbackmann; 11-15-06 at 12:33.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Results ? where ?

    Give me a for example (hint: output data) and I'll try to help. I don't understand the requirement very well -- 1 month grace, but is that a month following (i.e. next month) or preceding (i.e. last month) ?

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    The results that I want are in the datresult table.

    i need the year to increment by 1 if this is the second
    or more dat per bene in the 11 months from the 1st dat.

    if it is the 2nd dat per bene per year and it is < 1 year then the datyear doen't increase

    if it is > that 1 year since 1st dat then the datyear should increment.



    This is the proceedure to create the desired results.

    create proceedure thisisit
    is
    bene varchar2(15);
    datyear number;
    datcnt number;
    datdate date;
    gracedate date;
    number;

    BEGIN

    execute immediate 'truncate table datresult';
    datcnt := 0;
    datyear :=1;
    x := 0;
    for tbl_cur IN
    (select * from dat order by 1,2) loop
    x := x + 1;
    bene := tbl_cur.bene;
    datdate := tbl_cur.datdate;

    if x = 1 then
    gracedate := add_months(datdate, 11);
    end if;


    if datdate >= gracedate and datcnt >= 2 then
    datyear := datyear + 1;
    datcnt := 1;
    gracedate := add_months(gracedate, 11);
    else
    datcnt := datcnt + 1;
    end if;

    insert into datresult
    values(bene, datdate, datcnt, datyear) ;
    commit;

    end loop;

    end;

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Change in understanding

    The specs are different for the below which may make it easier using analytics.

    The gracedate is alway OCTOBER.

    2006 Year one any dats are compared for OCT1 2006 to OCT31 2007
    Unless 2 or more Dats prior to OCT1 2007 then 2007 starts OCT1 2007

    In case anyone tring this.

    Still working on myself cursor appears to be only option.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Sorry about that, I thought the table datresult was part of your data as well.

    Well, I just transposed your code (after doing a litle modification) into a technique I found on AskTom over this link. I tried an Analytic solution, but couldn't come up with something straight. The downside with this approach is that you must reset g_gracedate everytime before you run the query.

    I based the output on what your thisisit procedure does, not the sample data (they differ), so here it is:
    Code:
    SQL> create or replace package mypkg
      2  is
      3     function rank( p_beneanddate in varchar2 ) return number;
      4     pragma restrict_references( rank, wnds, rnds );
      5     pragma restrict_references( mypkg, wnds, rnds, wnps, rnps );
      6     g_datcnt        number          default 0;
      7     g_datyear       number          default 1;
      8     g_gracedate     date            default null;
      9     g_bene          datresult.bene%type    default null;
     10  end;
     11  /
    
    Package created.
    
    SQL> create or replace package body mypkg
      2  is
      3     function rank( p_beneanddate in varchar2 ) return number is
      4             l_date  date    default to_date( substr( p_beneanddate, length( p_beneanddate ) - 13 ), 'yyyymmddhh24miss' );
      5             l_bene  datresult.bene%type    default substr( p_beneanddate, 1, length( p_beneanddate ) - 14 );
      6     begin
      7             if g_gracedate is null or g_bene <> l_bene
      8             then
      9                     g_gracedate := add_months( l_date, 11 );
     10                     g_datcnt    := 0;
     11                     g_datyear   := 1;
     12                     g_bene      := l_bene;
     13             end if;
     14             if l_date >= g_gracedate and g_datcnt >= 2
     15             then
     16                     g_datyear   := g_datyear + 1;
     17                     g_datcnt    := 1;
     18                     g_gracedate := add_months( g_gracedate, 11 );
     19             else
     20                     g_datcnt := g_datcnt + 1;
     21             end if;
     22             return g_datyear;
     23     end;
     24  end;
     25  /
    
    Package body created.
    
    SQL> exec mypkg.g_gracedate := null;
    
    PL/SQL procedure successfully completed.
    
    SQL> select t.*, mypkg.rank( bene || to_char( datdate, 'yyyymmddhh24miss' ) ) my_datyear
      2    from (
      3  select *
      4    from datresult
      5   order by 1, 2
      6         ) t
      7  /
    
    BEN DATDATE   DATCNT DATYEAR MY_DATYEAR
    --- --------- ------ ------- ----------
    JOE 01-NOV-05      1       1          1
    JOE 01-JUN-06      2       1          1
    JOE 01-OCT-06      1       2          2
    JOE 01-DEC-06      2       2          2
    JOE 01-MAR-07      3       2          2
    JOE 01-NOV-07      1       3          3
    JOE 01-OCT-08      2       3          3
    JOE 01-DEC-08      1       4          4
    JOE 01-JAN-09      2       4          4
    JOE 01-JUN-09      3       4          4
    
    10 rows selected.
    
    SQL> exec mypkg.g_gracedate := null;
    
    PL/SQL procedure successfully completed.
    
    SQL> select bene,
      2         datdate,
      3         datcnt,
      4         datyear,
      5         row_number( ) over( partition by bene, my_datyear order by datdate ) my_datcnt,
      6         my_datyear
      7    from (
      8  select t.*, mypkg.rank( bene || to_char( datdate, 'yyyymmddhh24miss' ) ) my_datyear
      9    from (
     10  select *
     11    from datresult
     12   order by 1, 2
     13         ) t
     14   where rownum > 0
     15         )
     16   order by 1, 2
     17  /
    
    BEN DATDATE   DATCNT DATYEAR MY_DATCNT MY_DATYEAR
    --- --------- ------ ------- --------- ----------
    JOE 01-NOV-05      1       1         1          1
    JOE 01-JUN-06      2       1         2          1
    JOE 01-OCT-06      1       2         1          2
    JOE 01-DEC-06      2       2         2          2
    JOE 01-MAR-07      3       2         3          2
    JOE 01-NOV-07      1       3         1          3
    JOE 01-OCT-08      2       3         2          3
    JOE 01-DEC-08      1       4         1          4
    JOE 01-JAN-09      2       4         2          4
    JOE 01-JUN-09      3       4         3          4
    
    10 rows selected.
    
    SQL>
    Last edited by JMartinez; 11-19-06 at 13:17.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Was playing around with it a litle bit and got an analytic solution without requiring the package at all. This is based on your original dat table data.
    Code:
    SQL> select * from dat order by 1, 2;
    
    BENE            DATDATE
    --------------- ---------
    JOE             01-NOV-05
    JOE             01-JUN-06
    JOE             01-OCT-06
    JOE             01-DEC-06
    JOE             01-MAR-07
    JOE             01-NOV-07
    JOE             01-OCT-08
    JOE             01-DEC-08
    JOE             01-JAN-09
    JOE             01-JUN-09
    
    10 rows selected.
    
    SQL> with dates
      2    as (
      3  select rownum - 1 + mindate d
      4    from dual, ( select min( datdate ) mindate, max( datdate ) maxdate from dat ) d
      5  connect by level <= maxdate - mindate + 1
      6       )
      7  select bene, datdate,
      8         row_number( ) over( partition by rnk order by datdate ) datcnt,
      9         dense_rank( ) over( order by rnk ) datyear
     10    from (
     11  select t.*,
     12         case when nvl( lag( max_rn ) over( order by rownum ), max_rn ) <> max_rn
     13               and lag( max_rn, 2 ) over( order by rownum ) <> lag( max_rn ) over( order by rownum )
     14              then lag( max_rn ) over( order by rownum )
     15              else max_rn
     16          end rnk
     17    from (
     18  select t.*,
     19         max( rn ) over( order by rownum ) max_rn
     20    from (
     21  select t.*,
     22         case when nvl( lag( mb ) over( order by rownum ), 10 ) > mb
     23              then rownum
     24          end rn
     25    from (
     26  select dat.*, dates.*,
     27         mod( trunc( months_between( dates.d, min( dates.d ) over( ) ) ), 11 ) mb
     28    from dat right outer join dates
     29      on dat.datdate = dates.d
     30   order by dates.d
     31         ) t
     32   order by d
     33         ) t
     34   order by d
     35         ) t
     36   where datdate is not null
     37    order by d
     38         )
     39   order by 1, 2
     40  /
    
    BENE            DATDATE       DATCNT    DATYEAR
    --------------- --------- ---------- ----------
    JOE             01-NOV-05          1          1
    JOE             01-JUN-06          2          1
    JOE             01-OCT-06          1          2
    JOE             01-DEC-06          2          2
    JOE             01-MAR-07          3          2
    JOE             01-NOV-07          1          3
    JOE             01-OCT-08          2          3
    JOE             01-DEC-08          1          4
    JOE             01-JAN-09          2          4
    JOE             01-JUN-09          3          4
    
    10 rows selected.
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •