'How to filter out rows based on information in other columns and rows
I have a pricing policy table that determines the price a customer is given based on qty purchased as per below.
| debtor_code | stock_code | min_quantity | default_price | contract_price |
|---|---|---|---|---|
| 2393 | GRBAG100GTALL-50 | 0 | 295 | 236 |
| 2393 | GRBAG100GTALL-50 | 5 | 295 | 265.5 |
| 2393 | GRBAG100GTALL-50 | 10 | 295 | 221.25 |
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 SQL Server 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:
| debtor_code | stock_code | min_quantity | default_price | contract_price |
|---|---|---|---|---|
| 2393 | GRBAG100GTALL-50 | 0 | 295 | 236 |
| 2393 | GRBAG100GTALL-50 | 10 | 295 | 221.25 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|