先进先出出入库

DROP TABLE #InTab
CREATE TABLE #InTab (IId INT,SKUID INT,IQty INT,CanUsedQty INT,SumInQty INT,IsNew BIT,CompanyCode NVARCHAR(20))
INSERT INTO #InTab
     ( IId, IQty,SKUID,CompanyCode )
VALUES    
        --( 1,6,1 ,'000051'),
        --( 2,2 ,1,'000051'),
        ( 3,4 ,1,'000071'),
        ( 4,1 ,1,'000071')

UPDATE OIT
SET SumInQty=ISNULL((
SELECT SUM(IIT.IQty) FROM #InTab AS IIT WHERE IIT.IId<=OIT.IId
)
,0),OIT.CanUsedQty=IQty
FROM #InTab AS OIT

DROP TABLE #OutTab
CREATE TABLE #OutTab (OId INT ,OQty INT,SKUID INT,NeedOutQty INT,SumOutQty INT,IsNew BIT,CompanyCode NVARCHAR(20))
INSERT INTO #OutTab
     ( OId, OQty,SKUID )
VALUES 
( 1,8,1),
--( 2,3,1),
--( 3,6,1),
( 4,1,1)

UPDATE OIT
SET SumOutQty=ISNULL((
SELECT SUM(IIT.OQty) FROM #OutTab AS IIT WHERE IIT.OId<=OIT.OId
)
,0),OIT.NeedOutQty=OQty
FROM #OutTab AS OIT

DROP TABLE #TmpInData
CREATE TABLE #TmpInData(IId INT)
--将入库记录标记为已处理(1,2,3)10
UPDATE IT
SET IT.IsNew =1,IT.CanUsedQty=0
OUTPUT Inserted.IId INTO #TmpInData
 FROM #InTab AS IT
JOIN #OutTab AS OT ON OT.SKUID = IT.SKUID
WHERE IT.SumInQty<=OT.SumOutQty

DROP TABLE #TmpOutData
CREATE TABLE #TmpOutData(OId INT)
----将出库记录标记为已处理(1,2)5
UPDATE OT
SET OT.IsNew=1,OT.NeedOutQty=0,OT.CompanyCode=IT.CompanyCode
OUTPUT Inserted.OId INTO #TmpOutData
 FROM #InTab AS IT
JOIN #OutTab AS OT ON OT.SKUID = IT.SKUID
WHERE OT.SumOutQty<=IT.SumInQty

--SELECT * FROM #TmpOutData

----将出库记录和入库记录的差值赋值给已出库数量
DROP TABLE #TmpDiff
CREATE TABLE #TmpDiff([SKUID] int, [IId] int, [SumInQty] int, [SumOutQty] int,  [OId] INT,Mode INT )
INSERT INTO #TmpDiff( SKUID, IId, SumInQty, SumOutQty,  OId,Mode )
SELECT 
--TOP 1 IT.SKUID,IT.IId ,IT.SumInQty,OT.SumOutQty,OT.OQty,OT.OId
 IT.SKUID,MAX(IT.IId) IId ,MAX(IT.SumInQty) SumInQty,MIN(OT.SumOutQty) SumOutQty,MIN(OT.OId) OId,1 Mode
FROM #InTab AS IT
JOIN #OutTab AS OT ON OT.SKUID = IT.SKUID
WHERE IT.SumInQty<=OT.SumOutQty 
AND OT.OId NOT IN(SELECT * FROM #TmpOutData AS TID)
GROUP BY IT.SKUID
UNION ALL
SELECT IT.SKUID,MIN(IT.IId) IId ,MIN(IT.SumInQty) SumInQty,MAX(OT.SumOutQty) SumOutQty,MAX(OT.OId) OId,2 Mode
FROM #InTab AS IT
JOIN #OutTab AS OT ON OT.SKUID = IT.SKUID
WHERE OT.SumOutQty<=IT.SumInQty AND IT.IId NOT IN(SELECT * FROM #TmpInData AS TID)
GROUP BY IT.SKUID

SELECT * FROM #TmpDiff AS TD
--SELECT * FROM #InTab AS IT
--SELECT * FROM #OutTab AS OT

UPDATE ot
SET ot.OQty=ot.OQty-( td.SumOutQty-td.SumInQty),ot.NeedOutQty=CASE WHEN td.Mode=1 THEN 0  ELSE ot.OQty-( td.SumOutQty-td.SumInQty) END 
FROM  #OutTab ot
JOIN #TmpDiff td ON td.OId = ot.OId
WHERE ot.NeedOutQty>0

UPDATE it
SET it.CanUsedQty=td.SumInQty-td.SumOutQty
FROM  #InTab it
JOIN #TmpDiff td ON td.IId = it.IId
WHERE it.CanUsedQty>0

INSERT INTO #OutTab
     ( OId, OQty, SKUID, NeedOutQty, SumOutQty, IsNew,CompanyCode )
SELECT 0, td.SumOutQty-td.SumInQty, ot.SKUID, CASE WHEN td.Mode=2 THEN 0  ELSE td.SumOutQty-td.SumInQty END , NULL, 0,IT.CompanyCode
FROM  #OutTab ot
JOIN #TmpDiff td ON td.OId = ot.OId
JOIN #InTab AS IT ON it.IId=td.IID
WHERE td.SumOutQty>td.SumInQty

SELECT * FROM #OutTab AS OT

SELECT * FROM #InTab AS IT