Forum Stats

  • 3,734,027 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

Generating column based on sparsed values

548091
548091 Member Posts: 103
edited November 2010 in SQL & PL/SQL
Hi,

I have the following data in Prod table:
WK_NUM	Prod	Qty
-------------   ------          -----
1	A	0
2	A	10
3	A	20
4	A	-15
5	A	-20
6	A	10
7	A	5
8	A	50
9	A	15
10	A	0
11	A	-10
12	A	-30
13	A	-40
14	A	100
15	A	-20
16	A	40
17	A	35
18	A	20
19	A	10
I need to generate the below Result column:
WK_NUM	Prod	Qty	Result	Comment on how to generate the Result column
-------------   ------          -----           --------        --------------------------------------------------------------------
1	A	0	0	Value is zero, so copy as is
2	A	10	10	Value is positive, so copy as is
3	A	20	20	Same
4	A	-15	0	Negative so make zero, Keep it zero till the point 
                                                                where the total does not become positive again
5	A	-20	0	
6	A	10	0	
7	A	5	0	
8	A	50	30	Addition of values from WK_NUM=4 to 
                                                                WK_NUM=8 becomes positive again
9	A	15	15	Value is positive, so copy as is
10	A	0	0	
11	A	-10	0	Negative so make zero, Keep it zero till the point 
                                                                where the total does not become positive again
12	A	-30	0	
13	A	-40	0	
14	A	100	20	Addition of values from WK_NUM=11 to 
                                                                WK_NUM=14 becomes positive again
15	A	-20	0	Negative so make zero, Keep it zero till the point 
                                                                where the total does not become positive again
16	A	40	20	Addition of values from WK_NUM=15 to 
                                                                WK_NUM=16 becomes positive again
17	A	35	35	Value is positive, so copy as is
18	A	20	20	Value is positive, so copy as is
19	A	10	10	Value is positive, so copy as is
Hope I have been clear in providing the correct details.

Any hints/solutions on how to generate the above Result column would be appreciated.

Thanks in advance,

Answers

  • 548849
    548849 Member Posts: 1,083
    Why did you miss values for week_num 6 & 7 ? 
    
    The result is 0 whereas i was expecting 5 & 10.
    
    Could you also post some create and insert scripts of the table ?
  • 548091
    548091 Member Posts: 103
    Hi,

    Apologies for belated reply.

    There's no missing values for wk_num 6 & 7.

    Below is the part of the data as to how the 0 appears for negative values. The "Logical Running Sum" is not a physical column, but I am giving it below just for understanding, and using this logical column the Result column is having 0 for negative values.
    WK_NUM	Product	Qty	Logical Running Sum	Result
    4	A	-15	-15	                0
    5	A	-20	-35	                0
    6	A	10	-25	                0
    7	A	5	-20	                0
    8	A	50	30	                30
    Same way it works for wk_num's 11 to 14 and wk_num's 15 to 16.

    Looking forward to any solutions.

    Thanks,
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Whenever you post any problem please post the DDL & DML for that environment. Or,
    WITH tab
    AS
       (
           SELECT <column list> FROM <table name>
           UNION ALL
           SELECT <column list> FROM <table name>
       )
    SELECT *
    FROM tab;
    This will help us to concentrate on your main problem rather preparing the environment first.

    Got me?

    Regards.

    Satyaki De.
  • 548091
    548091 Member Posts: 103
    Below is the script for the data which is in the Prod table:
    with prod as (
        select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
        select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
        select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
        select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
        select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
        select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
        select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
        select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
        select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
        select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
        select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
        select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
        select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
        select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
        select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
        select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
        select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
        select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
        select 19 as wk_num, 'A' as prod, 10 as qty from dual
    )    
    select *
    from prod;
    hope the above helps.
    Thanks,
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    I think I'm missing one requirement here, on what do you want your running total exactly?
    SQL> with prod as (
      2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
      3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
      4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
      5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
      6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
      7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
      8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
      9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
     10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
     11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
     12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
     13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
     14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
     15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
     16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
     17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
     18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
     19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
     20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
     21  )    
     22  select wk_num
     23  ,      prod
     24  ,      qty
     25  ,      sum(qty) over (order by wk_num) running_total
     26  ,      case when sign(sum(qty) over (order by wk_num)) = -1 then 0
     27              else sum(qty) over (order by wk_num)
     28         end result
     29  from prod
     30  order by wk_num;
    
        WK_NUM P        QTY RUNNING_TOTAL     RESULT
    ---------- - ---------- ------------- ----------
             1 A          0             0          0
             2 A         10            10         10
             3 A         20            30         30
             4 A        -15            15         15
             5 A        -20            -5          0
             6 A         10             5          5
             7 A          5            10         10
             8 A         50            60         60
             9 A         15            75         75
            10 A          0            75         75
            11 A        -10            65         65
            12 A        -30            35         35
            13 A        -40            -5          0
            14 A        100            95         95
            15 A        -20            75         75
            16 A         40           115        115
            17 A         35           150        150
            18 A         20           170        170
            19 A         10           180        180
    
    19 rows selected.
  • 701278
    701278 Member Posts: 35
    Hi Mak2,

    since your requirement is based on procedural rules, you could use the model clause like this:
    SQL> WITH prod AS (
      2  SELECT 1 wk_num,'A' prod, 0 qty FROM dual UNION ALL
      3  SELECT 2 ,'A', 10 FROM dual UNION ALL
      4  SELECT 3 ,'A', 20 FROM dual UNION ALL
      5  SELECT 4 ,'A', -15 FROM dual UNION ALL
      6  SELECT 5 ,'A', -20 FROM dual UNION ALL
      7  SELECT 6 ,'A', 10 FROM dual UNION ALL
      8  SELECT 7 ,'A', 5 FROM dual UNION ALL
      9  SELECT 8 ,'A', 50 FROM dual UNION ALL
     10  SELECT 9 ,'A', 15 FROM dual UNION ALL
     11  SELECT 10 ,'A', 0 FROM dual UNION ALL
     12  SELECT 11 ,'A', -10 FROM dual UNION ALL
     13  SELECT 12 ,'A', -30 FROM dual UNION ALL
     14  SELECT 13 ,'A', -40 FROM dual UNION ALL
     15  SELECT 14 ,'A', 100 FROM dual UNION ALL
     16  SELECT 15 ,'A', -20 FROM dual UNION ALL
     17  SELECT 16 ,'A', 40 FROM dual UNION ALL
     18  SELECT 17 ,'A', 35 FROM dual UNION ALL
     19  SELECT 18 ,'A', 20 FROM dual UNION ALL
     20  SELECT 19 ,'A', 10 FROM dual
     21  ) SELECT wk_num, prod, qty, running_total, results FROM prod p
     22  MODEL
     23     PARTITION BY (prod)
     24     DIMENSION BY (wk_num)
     25     MEASURES (qty, 0 AS running_total, 0 AS results)
     26     RULES (
     27        running_total[ANY]=CASE
     28                             WHEN nvl(running_total[cv()-1], 0) >= 0 THEN qty[cv()]
     29                             ELSE nvl(running_total[cv()-1], 0) + qty[cv()]
     30                           END,
     31        results[ANY]=CASE
     32                       WHEN running_total[cv()] >= 0 THEN running_total[cv()]
     33                       ELSE 0
     34                     END
     35     );
    
        WK_NUM P        QTY RUNNING_TOTAL    RESULTS
    ---------- - ---------- ------------- ----------
             1 A          0             0          0
             2 A         10            10         10
             3 A         20            20         20
             4 A        -15           -15          0
             5 A        -20           -35          0
             6 A         10           -25          0
             7 A          5           -20          0
             8 A         50            30         30
             9 A         15            15         15
            10 A          0             0          0
            11 A        -10           -10          0
    
        WK_NUM P        QTY RUNNING_TOTAL    RESULTS
    ---------- - ---------- ------------- ----------
            12 A        -30           -40          0
            13 A        -40           -80          0
            14 A        100            20         20
            15 A        -20           -20          0
            16 A         40            20         20
            17 A         35            35         35
            18 A         20            20         20
            19 A         10            10         10
    
    19 rows selected.
    Regards,

    --
    Vincent
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Perhaps you are looking for this ->
    satyaki>
    satyaki>select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for Linux: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    Elapsed: 00:00:00.00
    satyaki>
    satyaki>
    satyaki>with prod as (
      2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
      3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
      4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
      5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
      6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
      7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
      8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
      9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
     10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
     11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
     12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
     13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
     14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
     15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
     16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
     17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
     18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
     19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
     20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
     21  )    
     22  select k.wk_num,
     23         k.prod,
     24         k.qty,
     25         k.running_sal,
     26         case
     27           when running_sal < 0 
     28           or qty < 0 then
     29             0
     30         else
     31           qty
     32         end result
     33  from (
     34          select wk_num,
     35                 prod,
     36                 qty,
     37                 sum(qty) over(order by wk_num) running_sal
     38          from prod
     39       ) k;
    
        WK_NUM P        QTY RUNNING_SAL     RESULT
    ---------- - ---------- ----------- ----------
             1 A          0           0          0
             2 A         10          10         10
             3 A         20          30         20
             4 A        -15          15          0
             5 A        -20          -5          0
             6 A         10           5         10
             7 A          5          10          5
             8 A         50          60         50
             9 A         15          75         15
            10 A          0          75          0
            11 A        -10          65          0
    
        WK_NUM P        QTY RUNNING_SAL     RESULT
    ---------- - ---------- ----------- ----------
            12 A        -30          35          0
            13 A        -40          -5          0
            14 A        100          95        100
            15 A        -20          75          0
            16 A         40         115         40
            17 A         35         150         35
            18 A         20         170         20
            19 A         10         180         10
    
    19 rows selected.
    
    Elapsed: 00:00:00.01
    satyaki>
    Regards.

    Satyaki De.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    My model clause article (in Japanese)
    http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html

    I used Least , greatest and model clause B-)

    In model clause,
    If runningSum is over than 0,
    I will regard runningSum was 0 B-)

    And Then I use greatest
    when runningSum is negative,I will change runningSum is 0 ;-)
    create table modelRun(WK_NUM,Qty) as
    select  1,  0 from dual union
    select  2, 10 from dual union
    select  3, 20 from dual union
    select  4,-15 from dual union
    select  5,-20 from dual union
    select  6, 10 from dual union
    select  7,  5 from dual union
    select  8, 50 from dual union
    select  9, 15 from dual union
    select 10,  0 from dual union
    select 11,-10 from dual union
    select 12,-30 from dual union
    select 13,-40 from dual union
    select 14,100 from dual union
    select 15,-20 from dual union
    select 16, 40 from dual union
    select 17, 35 from dual union
    select 18, 20 from dual union
    select 19, 10 from dual;
    
    select WK_NUM,Qty,greatest(runSum,0) as Result
      from modelRun
     model
    dimension by(WK_NUM)
    measures(Qty,0 as runSum)
    rules(
    runSum[any] order by WK_NUM =
     Least(presentv(runSum[cv()-1],runSum[cv()-1],0),0)
    +Qty[cv()]);
    
    WK_NUM  QTY  RESULT
    ------  ---  ------
         1    0       0
         2   10      10
         3   20      20
         4  -15       0
         5  -20       0
         6   10       0
         7    5       0
         8   50      30
         9   15      15
        10    0       0
        11  -10       0
        12  -30       0
        13  -40       0
        14  100      20
        15  -20       0
        16   40      20
        17   35      35
        18   20      20
        19   10      10
This discussion has been closed.