Hi expert friends
Please help I am stuck with a problem.
Here are my table values
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
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
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.
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
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,
when 2 then ---price point
WHEN 3 then --percent off
when 1 then --Amount off
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!!!!
Taking in a derived table and then take it outside ,maybe even if necessary computation or some sort of cte.
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?
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