I have a pricing policy table that determines the price a customer is given based on qty purchased as per below.

The pricing offered is based on the cheapest contract_price available for the lowest qty, meaning that the second row is obsolete as the min_quantity, and the cheaper price from the first row overrides the second row.

How can I use a Server SQL query to filter out obsolete rows like this as the first row supersedes it by having a cheaper contract_price at a lower min_quantity. The result should look like:

CodePudding user response：

use LEAD() to find the next tier's contract_price and compare with current level. Set the flag and filter out accordingly in the final query.

Based on assumption that price at higher tier (higher min_quantity value) should be cheaper than current tier.

with cte as
(
    select *,
           case when lead(contract_price) over (partition by debtor_code 
                                                    order by min_quantity) < contract_price
                then 1
                else 0 
                end as flag
    from   pricing
 )
 select *
 from   cte
 where  flag = 0

dbfiddle demo

EDIT :

The following query uses recursive cte to compare current row with previous row to determine the validity of the price

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

dbfiddle demo

Edit 2

A much simplified solution. First is to find the min(contract_price) in the sequence of min_quantity. Then simply compare the current contract_price with that. It is same or equal, it is valid

select * 
from
(
    select *, valid_price = min(contract_price) 
                            over (partition by debtor_code, stock_code
                                      order by min_quantity)
    from   pricing
) p
where contract_price <= valid_price

dbfiddle demo