1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Aggregate sequential production using plsql

Discussion in 'SQL PL/SQL' started by m25mathews@gmail.com, Jul 10, 2015.

  1. PLSQL wizards,

    Please can you help me with the below requirment .. Basically looking to sum the production for sequential production where production is > 0 , then group the result by entityname,year of production, sum of total production, sequential number of days. Please refer the attachment for detailed explanation of the situation and requirement. Please help
     

    Attached Files:

  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Please check the below code. I have created one table according to your table structure and inserted that data into that table. I think it's lengthy but it will give some idea on this.


    Code (Text):
    DECLARE

    CURSOR c1 IS
    SELECT rownum rn  ,x.*
    FROM (SELECT * FROm XX_TEST_EMP ORDER BY prod_dt ) x
    ORDER BY rn;


    CURSOR c2(cp_rn NUMBER) IS
    SELECT *
    FROM  (
      SELECT rownum rn  ,x.*
      FROM (SELECT * FROm XX_TEST_EMP ORDER BY prod_dt) x
      )
    WHERE  rn < cp_rn
    ORDER BY prod_dt desc;

    CURSOR c3(cp_rn NUMBER) IS
    SELECT *
    FROM  (
      SELECT rownum rn  ,x.*
      FROM (SELECT * FROm XX_TEST_EMP ORDER BY prod_dt ) x
      ORDER BY rn
      )
    WHERE  rn >= cp_rn;

    ln_prev_cnt NUMBER;
    ln_cnt  NUMBER;
    ln_tot_cnt  NUMBER;
    lp_prev_dt DATE;

    BEGIN
      lp_prev_dt  := null;
      DBMS_output.put_line( ' Rn  ~  Completion Name  ~  Prod Dt  ~  Prod  ~  Seq date cnt  ') ;
      FOR i in c1 LOOP
      ln_tot_cnt := 0;
      ln_prev_cnt := 0;
      ln_cnt  := 0;
      lp_prev_dt  := null;
      IF(i.prod <>0  )THEN
      FOR j in c2(i.rn) LOOP
      IF( (j.prod_dt +1 <> lp_prev_dt) AND lp_prev_dt IS NOT NULL OR ( j.prod =0) OR (i.prod_dt-1 <> j.prod_dt AND i.rn-1 = j.rn)) THEN
      EXIT;
      END IF;
      ln_prev_cnt := ln_prev_cnt +1;
      lp_prev_dt := j.prod_dt;
      END LOOP;
      lp_prev_dt := null;
      FOR k in c3(i.rn) LOOP
      IF( (k.prod_dt -1 <> lp_prev_dt) AND lp_prev_dt IS NOT NULL OR ( k.prod =0) OR (i.prod_dt-1 <> k.prod_dt AND i.rn-1 = k.rn)) THEN
      EXIT;
      END IF;
      ln_cnt := ln_cnt +1;
      lp_prev_dt := k.prod_dt;
      END LOOP;
      END IF;
      ln_tot_cnt := ln_prev_cnt+ln_cnt;
      IF(ln_tot_cnt = 0) THEN
      ln_tot_cnt := null;
      END IF;
      DBMS_output.put_line( i.rn||' ~ '||i.completion_name||'~  '||i.prod_dt||' ~ '||i.prod||'  ~ '||ln_tot_cnt);
      -- Insert the data into one temp table
      END LOOP;

      --
      -- Here take the temp table use group by for to get the required output
      --

    END;
    /


    Regards
    Sambasiva Reddy.K
     
    Mattz likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can do it with PL/SQL:

    Code (Text):
    SQL> declare
      2          cursor get_prod_data is
      3          select completionname, to_char(prod_dt, 'RRRR') year, prod_dt, prod
      4          from agg_test
      5          where prod > 0;
      6
      7          v_prodsum number;
      8          v_consecdays number:=0;
      9          v_compname varchar2(200);
    10          v_year  number;
    11          v_prev_prod_dt date:=null;
    12          v_prev_prod     number;
    13  begin
    14          for prod_rec in get_prod_data loop
    15                  if v_prev_prod_dt is null then
    16                          v_prev_prod_dt := prod_rec.prod_dt;
    17                          v_prev_prod := prod_rec.prod;
    18                          v_prodsum := prod_rec.prod;
    19                          v_compname := prod_rec.completionname;
    20                          v_year:=prod_rec.year;
    21                          v_consecdays:=1;
    22                  elsif prod_rec.prod_dt - v_prev_prod_dt = 1 then
    23                          v_prodsum := v_prodsum + prod_rec.prod;
    24                          v_consecdays := v_consecdays + 1;
    25                          v_year:=prod_rec.year;
    26                          v_compname := prod_rec.completionname;
    27                          v_prev_prod_dt := prod_rec.prod_dt;
    28                  else
    29                          if v_consecdays > 1 then
    30                                  dbms_output.put_line(v_compname||'      '||v_year||'    '||v_consecdays||'      '||v_prodsum);
    31                          end if;
    32                          v_consecdays :=1;
    33                          v_prev_prod_dt := prod_rec.prod_dt;
    34                          v_prodsum := prod_rec.prod;
    35                  end if;
    36          end loop;
    37
    38          dbms_output.put_line(v_compname||'      '||v_year||'    '||v_consecdays||'      '||v_prodsum);
    39  end;
    40  /
    BANA 1 15H      2014    4       322.92
    BANA 1 15H      2014    2       26.34
    BANA 1 15H      2015    7       425.59
    BANA 1 15H      2015    3       141.81
    BANA 1 15H      2015    3       164.07

    PL/SQL procedure successfully completed.

    SQL>
     
     
    Mattz and sambuduk like this.