clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36292 in the last week).

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;
CodeGoods Validate Mass
0001 2021-10-14 00:00:00.000 5000
0002 2021-10-14 00:00:00.000 4000
0003 2021-10-15 00:00:00.000 4000
CodeGoods Validate Mass
0001 2021-10-14 00:00:00.000 1000
0001 2021-10-15 00:00:00.000 6000
0001 2021-10-16 00:00:00.000 10000
0002 2021-10-15 00:00:00.000 10000
0003 2021-10-15 00:00:00.000 10000
0005 2021-10-15 00:00:00.000 10000
 hidden batch(es)


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
CodeGoods Validate Mass
0001 2021-10-14 00:00:00.000 1000
0001 2021-10-15 00:00:00.000 6000
0002 2021-10-15 00:00:00.000 10000
0003 2021-10-15 00:00:00.000 10000
 hidden batch(es)


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
CodeGoods Validate Mass
0001 2021-10-14 00:00:00.000 1000
0001 2021-10-15 00:00:00.000 6000
0002 2021-10-15 00:00:00.000 10000
0003 2021-10-15 00:00:00.000 10000
 hidden batch(es)