7

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.

4
  • Does your 'Actual Process' do what you want, but you just don't want the multiple sub-queries? That's why I offered CROSS APPLY (deleted answer now) as a possible solution. The CROSS APPLY allows you to 'join' against the sub-query part as a table and pull whatever columns you need. Commented Feb 22, 2017 at 15:38
  • By now I only update the price, but there are new requirements and I don't want to use multiples subqueries if possible.,
    – McNets
    Commented Feb 22, 2017 at 15:40
  • so, why can't you use CROSS APPLY and SET o.Price = ca.Price, o.Base = ca.Base, o.AnyOtherColumn = ca.AnyOtherColumn? Commented Feb 22, 2017 at 15:42
  • Yes, I think so.
    – McNets
    Commented Feb 22, 2017 at 15:54

2 Answers 2

5

I used you dbfiddler code in the following solution that uses CROSS APPLY

declare @price_list table(product varchar(20), price int, date_price datetime, base int, active tinyint);
insert into @price_list values
('0125',  90, '2017-01-01', 1200, 0), 
('0125', 100, '2017-01-25', 1000, 1), 
('0125', 110, '2017-02-27', 500,  1),
('1200', 140, '2017-01-01', 2000, 0), 
('1200', 150, '2017-02-01', 1500, 1), 
('1200', 160, '2017-02-27', 1000, 1);

declare @orders table(product varchar(20), order_date datetime, price int, base int);
insert into @orders values
('0125', '2017-02-19', 100, 1000), 
('0125', '2017-02-20', 100, 1000),
('0125', '2017-02-21', 100, 1000), 
('0125', '2017-02-22', 100, 1000),
('0125', '2017-02-23', 100, 1000), 
('0125', '2017-02-28', 110,  500),
('1200', '2017-02-19', 150, 1500), 
('1200', '2017-02-20', 150, 1500),
('1200', '2017-02-21', 150, 1500), 
('1200', '2017-02-22', 150, 1500),
('1200', '2017-02-23', 150, 1500), 
('1200', '2017-02-28', 160, 1000);



declare @new_date_price datetime = '2017-02-21';


-- add a new price to the list
--
insert into @price_list values ('0125', 105, @new_date_price - 1, 1300, 1);
insert into @price_list values ('1200', 155, @new_date_price - 1, 1400, 1);


-- update orders price and base, according products price list date price
--
-- I'd like to avoid use 2 (or more) subqueries
--
UPDATE o
SET o.Price = ca.price
    ,o.base = ca.base
FROM @Orders o
CROSS APPLY (
    SELECT TOP 1 pl.price AS price
        ,pl.base AS 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
    ) ca
WHERE o.product IN (
        '0125'
        ,'1200'
        );--<<< select distinct product from inserted
-- final result
--
select * from @price_list order by product, date_price;
select * from @orders order by product, order_date;
4
  • Ok, let me chek it in my db, but it looks so good. Thanks.
    – McNets
    Commented Feb 22, 2017 at 16:01
  • Hi, I'm testing on my staging server, it works well, but by now all orders are updated. Because it will run on a trigger, do you think it would be possible to upgrade only orders that are affected by the new price list?
    – McNets
    Commented Feb 22, 2017 at 21:09
  • By new price list I mean using select price, base, date_price from inserted
    – McNets
    Commented Feb 22, 2017 at 21:21
  • Well - that 'sounds' reasonable - I can't immediately think of any reason that would not work... I would certainly 'test' that in a non-production environment. Commented Feb 22, 2017 at 21:28
0

First of all I want to thank @ScottHodgin for his work to solve my question.

Although his answer correctly solved the problem, it updates all active orders for a particular product. And I wanted to find a solution that updates only orders affected by new price list date.

Since this procedure is executed within a trigger, I need to generate a list of date intervals to compare with the date of the orders.

I'm adding a day to 'next_date' if there is no next PriceList record, just to compare with <=.

SELECT product, price, date_price, base, active,
       (SELECT TOP 1     pl.date_price
               FROM      PriceList pl
               WHERE     pl.product = i.product
               AND       pl.date_price > i.date_price
               AND       pl.active > 0
               ORDER BY  pl.date_price ASC) next_date
FROM inserted i

Once solved the problem of the date range, I only need to update orders within this range.

WITH PList AS
(
SELECT product, price, date_price, base, active,
       (SELECT TOP 1     pl.date_price
               FROM      PriceList pl
               WHERE     pl.product = i.product
               AND       pl.date_price > i.date_price
               AND       pl.active > 0
               ORDER BY  pl.date_price ASC) next_date
FROM inserted i
)
UPDATE     Orders
SET        price = PList.price,
           base  = PList.base,
           [status] = 'Updated'
FROM       Orders o
INNER JOIN Plist
ON         Plist.product = o.product 
AND        o.order_date >= Plist.date_price
AND        o.order_date < COALESCE(Plist.next_date, DATEADD(DAY, 1, Plist.next_date);

After inserting two records:

DECLARE @new_date_price datetime = '2017-02-21';

INSERT INTO PriceList VALUES 
('0125', 105, @new_date_price, 1300, 2),
('1200', 155, @new_date_price, 1400, 2);

This is the final result:

|product|order_date  |price|base|status |
|:------|:-----------|----:|---:|:------|
|0125   |Feb 19 2017 |  100|1000|       |
|0125   |Feb 20 2017 |  100|1000|       |
|0125   |Feb 21 2017 |  105|1300|Updated|
|0125   |Feb 22 2017 |  105|1300|Updated|
|0125   |Feb 23 2017 |  105|1300|Updated|
|0125   |Feb 28 2017 |  110| 500|       |
|1200   |Feb 19 2017 |  150|1500|       |
|1200   |Feb 20 2017 |  150|1500|       |
|1200   |Feb 21 2017 |  155|1400|Updated|
|1200   |Feb 22 2017 |  155|1400|Updated|
|1200   |Feb 23 2017 |  155|1400|Updated|
|1200   |Feb 28 2017 |  160|1000|       |

If someone are interested, I've setup a dbfiddle example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.