HELP:QUERY 1

[miraora]

Please look at the query. My requirement is to add the distributed sales untill opportunity rating is found not 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.
can give you column.
SKU, STORE_NUM,YEAR_WEEK_KEY, DISTRIBUTED_SALES_PLAN, PSW_FLAG, END_OF_WEEK_ONHAND,OPPORTUNITY_RATING.

OPPORTUNITY RATING WILL Be updated only where it is not null value.
1. sum(decode(psw_fald,0,0 DISTRIBUTED_SALES_PLaN)) untill it finds OPPERTUNITY_rating NOT NULL. - this is the total it calculates
2. when it finds the oppertunity_rating it will take the EOWH value for that particuler row( OPPERTUNITY_RATING NOT NULL) and divides it by current total and reset the toatal and same thing untill it finds next not null OPPETUNITy for that
perticular sku, store_num. as showen in data table above.
This the logic it using for please

Please help me.

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_weekWHERE  sku = 11769802
        AND  store_num = 96ORDER BY year_week_key DESC;

END_O_W_H	DISTRIBUTEDPL_SAL	TOTOL	OPPORTUNITY_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      [b]0.8034    <-------94/117[/b] 
2				12		[b]129<----------------should be 22(10+12)[/b]	
2				14		14     [b]0.0909<-------- 2/22[/b]

 

[SantaMufasa]

Devmiral, I noticed that you joined Tek-Tips on January 7. You have been very prolific at posting questions since you became a member, which is just fine.

So that we can get to know you a bit better, and so that we can provide the best answers for your needs, could you please tell us a bit about your professional/industrial environment?

What is the industry in which are you working?

How many programmers are in your IT department?

Does your organization provide you with in-service training?

How long have you been an Oracle programmer?

How many years has it been since you completed your college/university-level training?

What level of training have you completed relating to Oracle?

Did you receive your Oracle training in an educational environment?

Have you experience with a database vendor other than Oracle? If so, which one?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[miraora]

I was out of job for a while. I got bcak to oracel field. So i am really confused. I B>S in Electronics
SO I need GURUS help.
Thank you

 

[miraora]

Any HINTS Please

 

[Dagon]

I'm a bit confused by your sample data. You say you want to partition on opportunity_rating, but the sample data shows that as blank all the way down. That is probably why your total is just increasing all the time - it is partioning on a single null value.

 

[miraora]

Thank you for your response.
The values you see in the opportunity_rating is null.
Is there any way i can get that result of running total. which I explained as above

 

[Dagon]

It would help if you posted create table and sample data scripts, since your sample data isn't very well formatted and seems to be missing crucial things such as the non-null opportunity_rating and the sku/store_num.

As far as I can tell, you want:

a) For each combination of sku and store_num, you want to total up the distributed_planned_sales for all the rows in which opportunity_rating is null.

b) On encoutering a row with opportunity_rating not null and the same sku/store_num, you want to divide the EOWH value by the total calculated in a).

Is there just one row for each sku and store_num with opportunity_rating not null ? If not, how are you determining which set of null opportunity_rating rows is associated with which non-null opportunity_rating row ? If you simply start walking through the table, you cannot guarantee what order the rows will appear in, so you could get different results each time you run it. Is there some sort of sequencing in the table to ensure that a consistent set of figures is delivered ?

 

[miraora]

Thank you once again
There is composite key od SKU, STORE_NUM, and YEAR_WEEK_KEY.
for one SKU and STORE_num there will be at least 15 to 17 UNIQUE YEAR_WEEK_KEY.
so oppertunity_rating is calculated in reverse as it is desending in the query. It start adding untill finds first not_null value of Oppertunity_rating. and as soon as it finds . it devides that total by EOWH quantity. as you can see in the query data.

 

[Dagon]

If I was going to do this, I would probably do it in PL/SQL. However, in the interests of a good challenge, I have a go at it using analytic functions. I have simplified some of the tables slightly, but here is the result.

create table sku_data (sku number, store_Num number, year_week number, 
		                       planned_sales number, opportunity_rating number, eowh_fig number);
		
insert into sku_data values (11769802, 96, 200601, 7, null, -7);
insert into sku_data values (11769802, 96, 200602, 11, null, -11);
insert into sku_data values (11769802, 96, 200603, 8, null, -5);
insert into sku_data values (11769802, 96, 200604, 6, null, 3);
insert into sku_data values (11769802, 96, 200605, 13, null, 9);
insert into sku_data values (11769802, 96, 200606, 9, null, 22);
insert into sku_data values (11769802, 96, 200607, 7, null, 31);
insert into sku_data values (11769802, 96, 200608, 6, null, 38);
insert into sku_data values (11769802, 96, 200609, 6, null, 44);
insert into sku_data values (11769802, 96, 200610, 5, null, 50);
insert into sku_data values (11769802, 96, 200611, 6, null, 55);
insert into sku_data values (11769802, 96, 200612, 5, null, 61);
insert into sku_data values (11769802, 96, 200613, 5, null, 66);
insert into sku_data values (11769802, 96, 200614, 6, null, 71);
insert into sku_data values (11769802, 96, 200615, 8, null, 77);
insert into sku_data values (11769802, 96, 200616, 9, null, 85);
insert into sku_data values (11769802, 96, 200617, 10, 1, 94);
insert into sku_data values (11769802, 96, 200618, 12, null, 2);
insert into sku_data values (11769802, 96, 200619, 14, 1, 2);
		
select  sku, store_num, year_week, eowh_fig, opportunity_rating op_rat,planned_sales sales, total, 
case when opportunity_rating is not null then 
round(eowh_fig/(lag(total) over (partition by sku, store_num order by year_week)),4) end as comp_fig
from
(select sku, store_num, year_week, eowh_fig, opportunity_rating ,planned_sales, sum(planned_sales) 
over (partition by sku, store_num, next_op_rating order by year_week range between unbounded preceding and current row ) total,
next_op_rating
from
(select sku, store_num, year_week, planned_sales, opportunity_rating, eowh_fig, 
(select min(s2.year_week)
from sku_data s2
where s2.opportunity_rating is not null
and s2.sku       = s1.sku 
and s2.store_num = s1.store_num 
and s2.year_week > s1.year_week) as next_op_rating
from sku_data s1))
order by year_week

Resuts:

      SKU  STORE_NUM  YEAR_WEEK   EOWH_FIG     OP_RAT      SALES      TOTAL   COMP_FIG
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  11769802         96     200601         -7                     7          7
  11769802         96     200602        -11                    11         18
  11769802         96     200603         -5                     8         26
  11769802         96     200604          3                     6         32
  11769802         96     200605          9                    13         45
  11769802         96     200606         22                     9         54
  11769802         96     200607         31                     7         61
  11769802         96     200608         38                     6         67
  11769802         96     200609         44                     6         73
  11769802         96     200610         50                     5         78
  11769802         96     200611         55                     6         84
  11769802         96     200612         61                     5         89
  11769802         96     200613         66                     5         94
  11769802         96     200614         71                     6        100
  11769802         96     200615         77                     8        108
  11769802         96     200616         85                     9        117
  11769802         96     200617         94          1         10         10      .8034
  11769802         96     200618          2                    12         22
  11769802         96     200619          2          1         14         14      .0909

 

[miraora]

Thank you for help.
But it is not giving me the correct result.

 

[miraora]

I made some changes and it is giving me correct result till Total 117>>> that is 0.8034. after that it is not giving me correct result. It is adding 117+12 and the dividing so instead of .0909, it is giving me 0.3411

select  sku, store_num, year_week_key, end_of_week_on_hand, opportunity_rating op_rat,distributed_planned_sales, total, 
         case when opportunity_rating is not null then 
           round(end_of_week_on_hand/(lag(total) over 
   (partition by sku, store_num order by year_week_key 
   desc)),4)
         end as New_OPP_rat
 from
       (select sku, store_num, year_week_key, 
  end_of_week_on_hand, 
   opportunity_rating ,distributed_planned_sales,
              sum(decode 
  (psw_flag,0,0,distributed_planned_sales)) over (partition by sku, store_num,opportunity_rating 
                             order by year_week_key desc ) total,
                    next_op_rating
       from  (select sku , store_num, year_week_key, psw_flag,distributed_planned_sales, opportunity_rating,
                   end_of_week_on_hand,
                      (select min(s2.year_week_key)
                      from ss_sku_store_week s2
                      where s2.opportunity_rating is not null
                            and s2.sku       = s1.sku 
                            and s2.store_num = s1.store_num 
                            and s2.year_week_key > s1.year_week_key)           as next_op_rating
            from ss_sku_store_week s1))
where sku= 11769802 and store_num=96 
order by year_week_key

 

[Dagon]

You've not said what was wrong with the version I gave you. As far as I could tell from your badly formatted grid, it gave the required results. Instead you've given me an alternative version in which you've changed crucial things and told me that doesn't work. This is making it almost impossible for me to interpret what the requirements really are.

In your version, you've changed the partition key from next_op_rating to opportunity_rating and thereby entirely missed the point of what I was trying to do.

 

[miraora]

Thank You Dragon.
Sorry for the format of the code. I tried to post promperly formated code. Some how it misarranges again.

I changed the Opportunity_rating to next_op_rating but now it is giving me 3.6667 which is 44/12 Suppose to be 44/22.
Also I have to use next_op_rating desc becuase it calculates in reverse order in my table

select  sku, store_num, year_week_key, end_of_week_on_hand, opportunity_rating op_rat,distributed_planned_sales, total, 
         case when opportunity_rating is not null then 
           round(end_of_week_on_hand/(lag(total) over (partition by sku, store_num order by year_week_key  desc )),4)
         end as New_OPP_rat
  from
       (select sku, store_num, year_week_key, end_of_week_on_hand, opportunity_rating ,distributed_planned_sales,
                sum(decode (psw_flag,0,0,distributed_planned_sales)) 
                      over (partition by sku, store_num, [b]next_op_rating order by year_week_key desc[/b] range between unbounded preceding and current row ) total,
                    next_op_rating
        from  (select sku , store_num, year_week_key, psw_flag,distributed_planned_sales, opportunity_rating,
                   end_of_week_on_hand,
                       (select min(s2.year_week_key)
                       from ss_sku_store_week s2
                       where s2.opportunity_rating is not null
                            and s2.sku       = s1.sku 
                            and s2.store_num = s1.store_num 
                            and s2.year_week_key > s1.year_week_key) as next_op_rating
                from ss_sku_store_week s1))
where sku= 11769802 and store_num=96 
order by year_week_key

 

[Dagon]

If you are going in reverse order, you will need to change the subquery to:

select min(s2.year_week_key)
from ss_sku_store_week s2
where s2.opportunity_rating is not null
and s2.sku = s1.sku
and s2.store_num = s1.store_num
and s2.year_week_key < s1.year_week_key

Also, I don't know where you get 44/22 from. Where does that appear in your test data ?

 

[Dagon]

In fact, it would have to be:

select max(s2.year_week_key)
from ss_sku_store_week s2
where s2.opportunity_rating is not null
and s2.sku = s1.sku
and s2.store_num = s1.store_num
and s2.year_week_key < s1.year_week_key

 

[miraora]

Yes Yes You Got it
That was Great.
Finally I got the Result.
Thank you So MuUch For your Help and Time