Setup
I've set up an example on rextester and dbfiddle.
Scenario
Price list: Its the products price list, one Products can have more than one active price, even a future price.
+---------+-------+------------+--------+--------+
| product | price | date_price | base | active |
+---------+-------+------------+--------+--------+
| 0125 | 90 | 01.01.2017 | 1200 | 0 |
| 0125 | 100 | 25.01.2017 | 1000 | 1 |
| 0125 | 110 | 27.02.2017 | 500 | 1 |
+---------+-------+------------+--------+--------+
| 1200 | 140 | 01.01.2017 | 2000 | 0 |
| 1200 | 150 | 01.02.2017 | 1500 | 1 |
| 1200 | 160 | 27.02.2017 | 1000 | 1 |
+---------+-------+------------+--------+--------+
Orders Pending orders have a price and an order's date
+---------+------------+-------+--------+
| product | order_date | price | base |
+---------+------------+-------+--------+
| 0125 | 19.02.2017 | 100 | 1000 |
| 0125 | 20.02.2017 | 100 | 1000 |
| 0125 | 21.02.2017 | 100 | 1000 |
| 0125 | 22.02.2017 | 100 | 1000 |
| 0125 | 23.02.2017 | 100 | 1000 |
| 0125 | 28.02.2017 | 110 | 500 |
+---------+------------+-------+--------+
| 1200 | 19.02.2017 | 150 | 1500 |
| 1200 | 20.02.2017 | 150 | 1500 |
| 1200 | 21.02.2017 | 150 | 1500 |
| 1200 | 22.02.2017 | 150 | 1500 |
| 1200 | 23.02.2017 | 150 | 1500 |
| 1200 | 28.02.2017 | 160 | 1000 |
+---------+------------+-------+--------+
Every time we add a new price in the list, we must update affected rows of pending orders.
For example if we add:
+---------+-------+------------+--------+--------+
| product | price | date_price | base | active |
+---------+-------+------------+--------+--------+
| 0125 | 105 | 21.02.2017 | 1300 | 1 |
| 1200 | 155 | 21.02.2017 | 1400 | 1 |
+---------+-------+------------+--------+--------+
The new price list must be:
+---------+------------+-------+--------+
| product | order_date | price | base |
+---------+------------+-------+--------+
| 0125 | 19.02.2017 | 100 | 1000 |
| 0125 | 20.02.2017 | 100 | 1000 |
| 0125 | 21.02.2017 | 105 | 1300 | *
| 0125 | 22.02.2017 | 105 | 1300 | * Affected rows
| 0125 | 23.02.2017 | 105 | 1300 | *
| 0125 | 28.02.2017 | 110 | 500 |
+---------+------------+-------+--------+
| 1200 | 19.02.2017 | 150 | 1500 |
| 1200 | 20.02.2017 | 150 | 1500 |
| 1200 | 21.02.2017 | 150 | 1500 | *
| 1200 | 22.02.2017 | 150 | 1500 | * Affectd rows between 21.02.2017 and 27.02.2017
| 1200 | 23.02.2017 | 150 | 1500 | *
| 1200 | 28.02.2017 | 160 | 1000 |
+---------+------------+-------+--------+
I would like to update affected records using a single query.
Due there is another price that starts at 27.02.2017, orders of 28.02.2017 are not affected by the inserted price.
Actual process
By now, I use a subquery that search for the first date that matches in the prices list table, but now I need to update base
field too. (And two or three more fields) and I'd like to avoid use two or more subqueries.
update @orders
set price = (select top 1 pl.price
from @price_list pl
where pl.product = o.product
and pl.date_price <= o.order_date
and active = 1
order by pl.date_price desc),
base = (select top 1 pl.base
from @price_list pl
where pl.product = o.product
and pl.date_price <= o.order_date
and active = 1
order by pl.date_price desc)
from @orders o
where o.product in ('0125', '1200'); --<<< select distinct product from inserted
Please, feel free to correct my text, I know my english grammar isn't good enough.