Query on running sum(cumulative diff)-not cdiff

Database
Enthusiast

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

7 REPLIES
Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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
Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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. 

Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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

Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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

Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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

Enthusiast

Re: Query on running sum(cumulative diff)-not cdiff

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!

Junior Contributor

Re: Query on running sum(cumulative diff)-not cdiff

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