Help with Analytical Function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Help with Analytical Function

  1. #1
    Join Date
    Jan 2007
    Posts
    13

    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)

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Could you post create table statement and insert into..?
    It will be easy to wokk out.

    Tamil

  3. #3
    Join Date
    Jan 2007
    Posts
    13
    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)
    )

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    And who is going to write INSERT statement?

    Tamil

  5. #5
    Join Date
    Jan 2007
    Posts
    13
    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);
    Last edited by devmiral; 01-15-2007 at 12:04 PM.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  7. #7
    Join Date
    Jan 2007
    Posts
    13
    Sorry About That
    I updated the Insert Statemnt for you.

  8. #8
    Join Date
    Jan 2007
    Posts
    13
    Any Help

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
    Last edited by tamilselvan; 01-17-2007 at 02:54 PM.

Posting Permissions

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


Click Here to Expand Forum to Full Width