表A
_________________________________________
ILE No |Post Date |Opening Qty | In Qty| Out Qty|
-----------------------------------------
1 30/06/20 500.00
4 30/06/20 300.00
5 01/07/20 250.00
9 05/07/20 300.00
表B
---------------------------------------------------------
Inbound Ent No | Outbound Ent No | Posting Date |Quantity|
----------------------------------------------------------
1 0 30/06/20 500
1 2 01/07/20 -200
1 3 02/07/20 -100
1 4 02/07/20 -50
4 0 30/06/20 300
4 7 01/07/20 -50
4 8 02/07/20 -100
我有上面两个表,上面有结构和数据。
我正在接受用户输入@startdate 02/07/20。我正试图计算";数量;表B中的字段在字段"中更新;期初数量;表A基于A。ILE编号=B。Inbound Ent No因此,在本例中,如果用户输入@startdate为"01/07/2020",则查询应计算数量总和来自表B的ILE 1号为300(500-200(。
我想你想用一个group by来对B求和,并用它来更新a。
UPDATE a
SET [Outbound Ent No] = t.[Total]
FROM TableA a
INNER JOIN (
SELECT [Inbound Ent No], SUM(Quantity) as [Total]
FROM TableB
WHERE [Posting Date] >= @startdate
GROUP BY [Inbound Ent No]
) t
ON t.[Inbound Ent No] = a.[ILE No]