Переделать SQL запрос

Question

Есть 2 таблицы: Заказы и остатки по системе

create table Orders (
[CodeGoods] [nvarchar](50) NULL,
[Validate] [datetime] NULL,
[Mass] [int] NULL
);
insert into Orders
values 
('0001', '20211014', 5000),
('0002', '20211014', 4000),
('0003', '20211015', 4000);
SELECT * FROM Orders;
create table Rest (
[CodeGoods] [nvarchar](50) NULL,
[Validate] [datetime] NULL,
[Mass] [int] NULL
);
insert into Rest
values 
('0001', '20211014', 1000),
('0001', '20211015', 6000),
('0001', '20211016', 10000),
('0002', '20211015', 10000),
('0003', '20211015', 10000),
('0005', '20211015', 10000);
SELECT * FROM rest;

В заказах содержатся товары, которые необходимы к отгрузке. Остатки считаются запросом.

Упрощено:

select Rest.CodeGoods
      ,Rest.Validate
      ,Rest.Mass
from Rest 
inner join Orders on Orders.CodeGoods = Rest.CodeGoods

На данный момент есть отчет, который выводит все остатки по товарам в заказе. Необходимо его сократить и привести к виду: количество которое в заказе по коду должно быть меньше или равно сумме количества на остатках.

Не могу понять как можно без циклов и временных таблиц сделать данный запрос. Так как остатки считаются и так не быстро (сек 20), если повесить циклы, то время еще увеличится.

Вопрос еще упирается в версию SQL-SERVER

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Answer 1

WITH
cte1 AS ( SELECT CodeGoods, 
                 Validate, 
                 Mass, 
                 SUM(Mass) OVER (PARTITION BY CodeGoods ORDER BY Validate) CumMass
          FROM Rest ),
cte2 AS ( SELECT cte1.CodeGoods,
                 cte1.Validate,
                 cte1.Mass,
                 COALESCE(LAG(CumMass) OVER (PARTITION BY cte1.CodeGoods ORDER BY cte1.Validate), 0) LagMass
          FROM cte1 )
SELECT cte2.CodeGoods,
       cte2.Validate,
       cte2.Mass
FROM cte2
JOIN Orders ON Orders.CodeGoods = cte2.CodeGoods
           AND Orders.Mass >= cte2.LagMass

fiddle

PS. Запрос не сравнивает даты в Orders и в Rest.

запустить его у себя не могу. Версия скуля древняя. Microsoft SQL Server 2008 R2 (RTM)

WITH
cte1 AS ( SELECT r1.CodeGoods, 
                 r1.Validate, 
                 r1.Mass, 
                 SUM(r2.Mass) CumMass,
                 COUNT(*) rownumber
          FROM Rest r1
          JOIN Rest r2 ON r1.CodeGoods = r2.CodeGoods
                      AND r1.Validate >= r2.Validate 
          GROUP BY r1.CodeGoods, 
                   r1.Validate, 
                   r1.Mass),
cte2 AS ( SELECT t1.CodeGoods,
                 t1.Validate,
                 t1.Mass,
                 COALESCE(t2.CumMass, 0) LagMass
          FROM cte1 t1
          LEFT JOIN cte1 t2 ON t1.CodeGoods = t2.CodeGoods
                           AND t1.rownumber = t2.rownumber+1 )
SELECT cte2.CodeGoods,
       cte2.Validate,
       cte2.Mass
FROM cte2
JOIN Orders ON Orders.CodeGoods = cte2.CodeGoods
           AND Orders.Mass >= cte2.LagMass

fiddle

БЛОГ НА HUSL

Переделать SQL запрос

Ответы (1 шт):