By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table pricing
(
debtor_code int,
stock_code varchar(20),
min_quantity int,
default_price decimal(10,2),
contract_price decimal(10,2)
)
insert into pricing values
(2393, 'GRBAG100GTALL-50', 0, 295, 236.00),
(2393, 'GRBAG100GTALL-50', 5, 295, 265.50),
(2393, 'GRBAG100GTALL-50', 7, 295, 270.50),
(2393, 'GRBAG100GTALL-50', 10, 295, 221.25),
(2393, 'GRBAG100GTALL-50', 12, 295, 240.25);
5 rows affected
with cte as
(
select *, rn = row_number() over (partition by debtor_code, stock_code
order by min_quantity)
from pricing
),
rcte as
(
select debtor_code, stock_code, rn, min_quantity, default_price, contract_price,
valid_price = contract_price, valid = 1
from cte
where rn = 1
union all
select c.debtor_code, c.stock_code, c.rn, c.min_quantity, c.default_price, c.contract_price,
valid_price = case when c.contract_price < r.contract_price
then c.contract_price
else r.contract_price
end,
valid = case when c.contract_price < r.contract_price
then 1
else 0
end
from rcte r
inner join cte c on r.rn = c.rn - 1
)
select *
from rcte
where valid = 1
| debtor_code | stock_code | rn | min_quantity | default_price | contract_price | valid_price | valid |
|---|---|---|---|---|---|---|---|
| 2393 | GRBAG100GTALL-50 | 1 | 0 | 295.00 | 236.00 | 236.00 | 1 |
| 2393 | GRBAG100GTALL-50 | 4 | 10 | 295.00 | 221.25 | 221.25 | 1 |