Help with Analytical Function

[devmiral]

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.

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;

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)

[tamilselvan]

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

Tamil

[devmiral]

Thank you for your concern

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)
)

[tamilselvan]

And who is going to write INSERT statement?

Tamil

[devmiral]

here is the insert statements for your insert

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);

[tamilselvan]

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

[devmiral]

Sorry About That
I updated the Insert Statemnt for you.

[devmiral]

Any Help

[tamilselvan]

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.

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