All Forums Database
sammy2014 5 posts Joined 11/14
03 Nov 2014
Query on running sum(cumulative diff)-not cdiff

Hi expert friends
 
Please help I am stuck with a problem. 
Here are my table values
Tab a
Promotion Location Item Date    Retail_Price Amount_type Discount priority
1               100        abc   08-01  20.00           2                10.00       5
2               100        abc   08-01  20.00           3                 20.00      4 
3               100        abc   08-01  20.00           1                 3.00         3
4               100        abc   08-01  20.00            1                1.00         2
5               100        abc   08-01  20.00            2                 4.00       1
6               200       bcd    08-01   15.00          1                10.00      2
7               200      bcd     08-01    15.00         2                 4.00       1
 
amount type: 1 -is (percent off) 2 is (price point) 3 is (amount off)
We start with first row and keep calculating the cumulative sums
Desired results are
Promotion Location Item Date    Retail_Price  Amount_Type Discounted_price 
1               100        abc   08-01  20.00         2                    10.00(just set the price to discount amount)
2               100        abc   08-01  20.00         3                     10.00-2.00(20% of 10)=8.00 
3               100        abc   08-01  20.00           1                    8.00-3.00=5.00
4               100        abc   08-01  20.00          1                      5.00-1.00=4.00
5               200       bcd    08-01   15.00          1                     15.00-10.00=5.00
6               200      bcd     08-01    15.00         2                      4.00
 
Final results:
Promotion Location Item Date    Retail_Price  Discounted_price 
1               100        abc   08-01  20.00        4.00
2               200        bcd   08-01  20.00        4.00
 
I was using the query below but it doesnt help me as it takes original retail_price into consideration for each row value

select  promotion,item_key,location_key,the_date,retail_price, amount_type,discount_price

,case  

Amount_Type

when 1 then   

(retail_price-(discount*retail_price)/100)

 

when 3 then --Percent off

(retail_price-discount)

when 2 then --Amount off per weight

discount

end

-

sum (case  

Amount_Type

when 1 then   

(retail_price-(discount*retail_price)/100)

 

when 3 then --Percent off

(retail_price-discount)

when 2 then --Amount off per weight

discount

end) over  (partition by item_key,location_key,the_date order by priority desc  rows between 1 preceding and 1 preceding)

from tab 1

 

 

 

 

Raja_KT 1246 posts Joined 07/09
03 Nov 2014

I could see that the discount amount is calculated as previous discounted row minus the percentage of the discounted amount.

How do you get these rows or am I missing something?

5 200 bcd 08-01 15.00 1 15.00-10.00=5.00
6 200 bcd 08-01 15.00 2 4.00

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sammy2014 5 posts Joined 11/14
04 Nov 2014

Thanks Raja for your reply . You are right. That exactly is my question. One way I could think was looking at previous Row and calculate new retail. I have a solution query coming to my mind. Will have to try n see when I will reach office today. 

sammy2014 5 posts Joined 11/14
05 Nov 2014

Raja sorry I didnt understand your question before. To get to result row 5 and 6,
For row 5:
If there is no promotion already present of higher priority then only the current promotion is applied on the original retail. For row 1, the promotion was to change the retail to 10(amount_type(or think of it as promotion type 2)- price point)
For row 6: (amount_type(promotion_type ) 2: so last promotional calculated retail is ignored and price is set to 4.00 straight away.
 
I hope I am clear now..
 
This is still a question

sammy2014 5 posts Joined 11/14
05 Nov 2014

So to make myself more clear guys, here is how I am trying to achieve it:
I am using an analytical function to look at previous row, and take that as my base retail and apply all promotional discounts on it. So for first row for a given store/item/day, it wont find a previous row hence null retail, so it will take the original retail(using coalesce function) and  apply the discount with highest priority. And this becomes my new_Retail. For second row, it should look at new_retail from previous_row and apply  discount to it and so on..
Here is the query below that I tried using:
select item,location,date,coalesce(max(new_retail) over (partition by item,location,date order by priority desc rows between 1 preceding and 1 preceding),retail_price) new_retail,discount,
case
                                                Amount_Type
                                                when 2 then   ---price point
                                                                       discount
                                                WHEN 3 then --percent off
                                                              new_retail-(discount*new_retail)/100
                                                when 1 then --Amount off
                                                                        new_Retail-discount
end   discounted_price   from tab1
Error that I get is :
new_retail not found in tab1
 
Please help!! Please let me know if you have any more questions!!!!
 

Raja_KT 1246 posts Joined 07/09
05 Nov 2014

Taking  in a derived table and then take it outside ,maybe  even if necessary computation or some sort of cte.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sammy2014 5 posts Joined 11/14
05 Nov 2014

I tried derived table but that doesnt help. Basically I cant use derived table because I am referring one column into another. Derived table is the lower level. Whereas these all needs to be at same level as input of one goes into another and vice versa. As in, I need new_retail for calculating discounted_price.
If I use derived table to calculate new_retail, that wont help me. as I need new_Retail from the previous row not the current row.
 
What is CTE?
 
Thanks!

dnoeth 4628 posts Joined 11/04
07 Nov 2014

The only way to get the expected result seems to be a recursive select.
You need a Volatile Table with a ROW_NUMBER() OVER (PARTITION BY location ORDER BY PROMOTION) as rn and a COUNT(*)  OVER (PARTITION BY location) as cnt
 
Then you start the recursion with rn = 1 and step through each row doing the calculation. In the final select get the last row using WHERE rn=cnt

Dieter

You must sign in to leave a comment.