Help with Analytical Function

Please look at the query. My requeirment in to add the distributed sales untill opportunity rating is found no null. As soon as it finds opportunity rating take that total and devide end_of_week_on_hand by the total. and reset that total and start adding again till another oppertunity rating is found. once found end_of_week_ on_hand/total.
Please look at the query and i am getting some result but I am not able to reset and devide by total and after I get those value I have to update opportunity_rating of the same table where it finds not null value of oppertunity rating.

Code:

---

SELECT /*+ INDEX (ss_sku_store_week ss_sku_str_wk_pk) */
          end_of_week_on_hand
    , DECODE(psw_flag,
              0, 0, distributed_planned_sales ) distributedplannedsales
    ,  SUM(DECODE(psw_flag, 0, 0, distributed_planned_sales ) )
          OVER ( PARTITION BY opportunity_rating ORDER BY year_week_key DESC) as totol,
            opportunity_rating
FROM  ss_sku_store_week
WHERE  sku = 11769802
AND    store_num = 96
ORDER BY year_week_key DESC;

---

Code:

---

END_OF_W_O_HAND        DISTRIBUTEDSALES        TOTOL        OPP._RATING

-7                        7                7       
-11                        11              18       
-5                        8              26       
3                        6                32       
9                        13                45       
22                        9                54       
31                        7                61       
38                        6                67       
44                        6                73       
50                        5                78       
55                        6                84       
61                        5                89       
66                        5                94       
71                        6                100       
77                        8                108       
85                        9                117       
94                        10                10        0.8034 <---this value  (94/117)
2                        12                129  <-------------------this value should be (10+12)=22       
2                        14                14        [B]0.0909[/B]<-----this value(2/22)

---

Could you post create table statement and insert into..?
It will be easy to wokk out.

Tamil

Thank you for your concern

Code:

---


CREATE TABLE SS_SKU_STORE_WEEK
(
  SKU                        NUMBER(10)        NOT NULL,
  STORE_NUM                  NUMBER(5)          NOT NULL,
  YEAR_WEEK_KEY              NUMBER(6)          NOT NULL,
  PLANNED_WEEK_FLAG          NUMBER(1)          NOT NULL,
  END_OF_WEEK_INVENTORY      NUMBER(10)        DEFAULT 0,
  DISTRIBUTED_PLANNED_SALES  NUMBER(10)        DEFAULT 0,
  PERCENT_OF_AVERAGE        NUMBER(7,4)        DEFAULT 0,
  PLANNED_RECEIPTS          NUMBER(10)        DEFAULT 0,
  CONTRIBUTING_ON_ORDER      NUMBER(10)        DEFAULT 0,
  WAREHOUSE_ALLOCATION      NUMBER(10)        DEFAULT 0,
  WEEKS_OF_SUPPLY            NUMBER(3)          DEFAULT 0,
  SELL_THROUGH_PERCENT      NUMBER(3,2)        DEFAULT 1,
  NEED                      NUMBER(10)        DEFAULT 0,
  STRATEGY_ID                NUMBER(10),
  END_OF_WEEK_ON_HAND        NUMBER(10),
  SHIPMENT_QUANTITY          NUMBER(10),
  OPPORTUNITY_RATING        NUMBER(8,4),
  SSK_WEEK_STATUS            NUMBER(1)          DEFAULT 0,
  PACKAGE_ID                NUMBER(10)        DEFAULT -1,
  VENDOR_ID                  NUMBER(6),
  VENDOR_MINIMUM_FLAG        NUMBER(1)          DEFAULT 1,
  SUBMITTED_ON              DATE,
  SUBMITTED_BY              VARCHAR2(30 BYTE),
  ROW_XNG_DT                DATE,
  ROW_XNG_USR                VARCHAR2(30 BYTE),
  CELL_BACKCOLOR            NUMBER(12)        DEFAULT 14211288,
  SKU_STORE_NUM              NUMBER(15),
  PSW_FLAG                  NUMBER(1),
  FUTURE_PLANNED_SALES      NUMBER(10),
  SYWK                      NUMBER(6)
)

---

And who is going to write INSERT statement?

Tamil

here is the insert statements for your insert

Code:

---

insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200629,7,1,-7,null,0);
insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200630,11,1,-11,null,0);
insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200630,8,1,-5,null,0);
insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200631,94,1,10,2,0);
insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200632,12,1,2,null,0);
insert into ss_sku_store_week
    (sku,store_num,year_week_key,distributed_planned_sales,psw_flag,end_of_week_on_hand,opportunity_rating,planned_week_flag)
    values( 123,1, 200633,14,1,2,1,0);

---

insert into ss_sku_store_week
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("TAMIL"."SS_SKU_STORE_WEEK"."PLANNED_WEEK_FLAG")

Tamil

Sorry About That
I updated the Insert Statemnt for you.

Any Help

What you need is to fill out the missing column value.
In 10g you can use ignore nulls in the analytic function to fill out missing values.

Code:

---


SQL> select end_of_week_on_hand eowh
  2      , year_week_key  ywk
  3      , DECODE(psw_flag, 0, 0, distributed_planned_sales )
  4        as  distplansales
  5      ,  opportunity_rating
  6      ,  last_value(opportunity_rating ignore nulls)
  7          over (order by year_week_key desc) op_rating
  8  from ss_sku_store_week
  9  ORDER BY year_week_key DESC
 10  /

      EOWH        YWK DISTPLANSALES OPPORTUNITY_RATING  OP_RATING
---------- ---------- ------------- ------------------ ----------
        2    200633            14                  1          1
        2    200632            12                            1
        10    200631            94                  2          2
      -11    200630            11                            2
        -5    200630            8                            2
        -7    200629            7                            2

6 rows selected.

---

Use this query in the from clause of your query.

Tamil