Atualizando pedidos em aberto de uma lista de preços dentro de um intervalo de datas

Eu usei seu código dbfiddler na seguinte solução que usa 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;

Em primeiro lugar, quero agradecer a @ScottHodgin por seu trabalho para resolver minha pergunta.

Embora sua resposta tenha resolvido corretamente o problema, ela atualiza todos os pedidos ativos de um determinado produto. E eu queria encontrar uma solução que atualizasse apenas os pedidos afetados pela nova data da lista de preços.

Como esse procedimento é executado dentro de uma trigger, preciso gerar uma lista de intervalos de datas para comparar com a data dos pedidos.

Estou adicionando um dia a 'next_date' se não houver próximo registro PriceList, apenas para comparar com <=.

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

Uma vez resolvido o problema do intervalo de datas, só preciso atualizar os pedidos dentro desse intervalo.

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);

Após inserir dois registros:

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);

Esse é o resultado final:

|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|       |

Se alguém estiver interessado, eu configurei um exemplo dbfiddle .