I have following data for promotion prices
| Promo_id | START_DATE | END_DATE | ITEM | SELLING_RETAIL | Change_Type | Change_Amount | Change_PERCENT | Change_Type_Desc |
|---|---|---|---|---|---|---|---|---|
| 2011 | 14-12-2023 | 20-12-2023 | 100261942 | 229 | 0 | NULL | -10 | Percent Off |
| 2012 | 17-12-2023 | 22-12-2023 | 100261942 | 229 | 0 | NULL | -20 | Percent Off |
| 2013 | 14-12-2023 | 19-12-2023 | 100263219 | 279 | 2 | 200 | NULL | Fixed Price |
| 2021 | 17-12-2023 | 21-12-2023 | 100263219 | 279 | 1 | -100 | NULL | Amount off |
I want to split the above date range as mentioned below
| Item | START_DATE | END_DATE | Promo_Price | Desc |
|---|---|---|---|---|
| 100261942 | 14-Dec-23 | 16-Dec-23 | 206.1 | 10% on 229 |
| 100261942 | 17-Dec-23 | 20-Dec-23 | 164.88 | 20% on 206.1 --cumulative |
| 100261942 | 21-Dec-23 | 22-Dec-23 | 183.2 | 20% on 229 |
| 100263219 | 14-Dec-23 | 16-Dec-23 | 200 | Fixed Price |
| 100263219 | 17-Dec-23 | 19-Dec-23 | 100 | 100 Amount off on 200 |
| 100263219 | 20-Dec-23 | 21-Dec-23 | 179 | 100 Amount off on 279 |
tried below query for splitting date range. However I am not able to derive promo_retail based on change_type
with cte1 as
(
select promo_id, percent, start_date as marker_date, 1 as type, b.item, b.SIMPLE_PROMO_RETAIL,
SELLING_RETAIL
from Table_A b
union all
select promo_id,percent, end_date+1 as marker_date, -1 as type, b.item, b.SIMPLE_PROMO_RETAIL
, SELLING_RETAIL
from Table_A b
),
cte2 as (
select promo_id, marker_date as begin_date,
lead(marker_date) over (order by marker_date) - 1 as end_date,
item, percent, SIMPLE_PROMO_RETAIL, SELLING_RETAIL,
sum(type) over (order by marker_date) as periods
from cte1
)
select a.promo_id, a.begin_date, a.end_date,a.item ,a.SELLING_RETAIL,
a.percent,periods
from cte2 a
where a.end_date is not null and periods > 0;
You can UNPIVOT the start- and end-dates and then use a MODEL clause to process it row-by-row and calculate the base price, any fixed discounts and any percentage discounts and overwrite, add or multiply (respectively) those with the previous row and then use LEAD to find the end-date of the range:
SELECT *
FROM (
SELECT item,
dt AS start_date,
LEAD(dt) OVER (PARTITION BY item ORDER BY dt)
- INTERVAL '1' SECOND AS end_date,
price
FROM (
SELECT item,
dt,
price
FROM (
SELECT item,
promo_id,
dt,
selling_retail,
change_type,
change_sign,
change_amount * change_sign AS change_amount,
COALESCE(
CASE change_sign
WHEN 1
THEN (100 + change_percent)/100
ELSE 100/(100 + change_percent)
END,
1
) AS multiplier,
ROW_NUMBER() OVER (PARTITION BY item ORDER BY dt, promo_id) AS rn
FROM table_name
UNPIVOT (
dt FOR change_sign IN (
start_date AS +1,
end_date AS -1
)
) u
)
MODEL
PARTITION BY (item)
DIMENSION BY (rn)
MEASURES (
promo_id,
dt,
selling_retail,
change_type,
change_sign,
change_amount,
multiplier,
0 AS base_price,
0 AS offset_price,
0 AS discount,
0 AS price
)
RULES (
base_price[1] = CASE
WHEN change_type[1] = 2
AND change_sign[1] = 1
THEN change_amount[1]
ELSE selling_retail[1]
END,
base_price[rn>1] = CASE
WHEN change_type[cv()] = 2
AND change_sign[cv()] = 1
THEN change_amount[cv()]
WHEN change_type[cv()] = 2
AND change_sign[cv()] = -1
THEN selling_retail[cv()]
ELSE base_price[cv()-1]
END,
offset_price[rn] = CASE
WHEN change_type[cv()] = 1
THEN change_amount[cv()]
ELSE 0
END + COALESCE(offset_price[cv()-1], 0),
discount[rn] = multiplier[cv()] * COALESCE(discount[cv()-1], 1),
price[rn] = base_price[cv()] * discount[cv()] + offset_price[cv()]
)
)
)
WHERE start_date < end_date;
Which, for the sample data:
CREATE TABLE table_name (Promo_id, START_DATE, END_DATE, ITEM, SELLING_RETAIL, Change_Type, Change_Amount, Change_PERCENT, Change_Type_Desc) AS
SELECT 2011, DATE '2023-12-14', DATE '2023-12-20', 100261942, 229, 0, NULL, -10, 'Percent Off' FROM DUAL UNION ALL
SELECT 2012, DATE '2023-12-17', DATE '2023-12-22', 100261942, 229, 0, NULL, -20, 'Percent Off' FROM DUAL UNION ALL
SELECT 2013, DATE '2023-12-14', DATE '2023-12-19', 100263219, 279, 2, 200, NULL, 'Fixed Price' FROM DUAL UNION ALL
SELECT 2021, DATE '2023-12-17', DATE '2023-12-21', 100263219, 279, 1, -100, NULL, 'Amount off' FROM DUAL;
Outputs:
| ITEM | START_DATE | END_DATE | PRICE |
|---|---|---|---|
| 100261942 | 2023-12-14 00:00:00 | 2023-12-16 23:59:59 | 206.1 |
| 100261942 | 2023-12-17 00:00:00 | 2023-12-19 23:59:59 | 164.88 |
| 100261942 | 2023-12-20 00:00:00 | 2023-12-21 23:59:59 | 183.2 |
| 100263219 | 2023-12-14 00:00:00 | 2023-12-16 23:59:59 | 200 |
| 100263219 | 2023-12-17 00:00:00 | 2023-12-18 23:59:59 | 100 |
| 100263219 | 2023-12-19 00:00:00 | 2023-12-20 23:59:59 | 179 |