库存先进先出的原则，计算库存里的商品屬于那一个(或几个)采购员

[weixin_44460901]

采购进货表
SKU 采购员 采购日期 采购数量
商品A 小明 2019/1/30 10
商品A 李华 2018/12/26 40
商品B 小张 2018/12/10 30
商品A 李小红 2018/11/3 25
商品B 小明 2019/1/26 22
商品B 李华 2018/12/5 30

商品库存表
SKU 数量
商品A 42
商品B 55


库存先进先出的原则，请问如何计算库存里的商品屬于那一个(或几个)采购员

SKU 库存数量 采购员 采购数量 采购员 采购数量 采购员 数量
商品A 42 小明 10 李货 32
商品B 55 小明 22 小张 30 李华 3

[weixin_44460901]

[一个低调的码农]

可以在审核单据后，写入数据表中！然后查询时根据此表进行汇总！

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#A') IS NOT NULL
DROP TABLE #A
GO

CREATE TABLE #A
(SKU VARCHAR(10),
 PURCHASER VARCHAR(10),
 PURCHASE_DATE DATE,
 AMOUNT INT)


INSERT INTO #A
SELECT '商品A','小明','2019/1/30',10 UNION ALL
SELECT '商品A','李华','2018/12/26',40 UNION ALL
SELECT '商品B','小张','2018/12/10',30 UNION ALL
SELECT '商品A','李小红',' 2018/11/3',25 UNION ALL
SELECT '商品B','小明',' 2019/1/26',22 UNION ALL
SELECT '商品B','李华',' 2018/12/5',22 



IF OBJECT_ID(N'TEMPDB.DBO.#B') IS NOT NULL
DROP TABLE #B
GO

CREATE TABLE #B
(SKU VARCHAR(10),
 INVENTORY INT)

INSERT INTO #B
SELECT '商品A',42 UNION ALL
SELECT '商品B',55

DECLARE @SQL VARCHAR(MAX)

;WITH CTE
AS
(SELECT B.*,A.PURCHASE_DATE,A.PURCHASER,
CASE WHEN B.INVENTORY>=SUB_TOTAL THEN A.AMOUNT
     WHEN B.INVENTORY<SUB_TOTAL AND B.INVENTORY>=SUB_TOTAL-A.AMOUNT THEN AMOUNT-(SUB_TOTAL-INVENTORY)
	 ELSE 0 END AS APPORTION
FROM #A A
JOIN #B B ON A.SKU=B.SKU
OUTER APPLY (SELECT SUM(AMOUNT) AS SUB_TOTAL FROM #A WHERE A.SKU=SKU AND PURCHASE_DATE>=A.PURCHASE_DATE) AS C),

CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY PURCHASE_DATE) AS SEQ FROM CTE
WHERE APPORTION<>0)

SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN SEQ='+CAST(SEQ AS VARCHAR)+' THEN PURCHASER ELSE '''' END) AS PURCHASER'+CAST(SEQ AS VARCHAR)+
             ',MAX(CASE WHEN SEQ='+CAST(SEQ AS VARCHAR)+' THEN APPORTION ELSE 0 END) AS APPORTION'+CAST(SEQ AS VARCHAR)
FROM
(SELECT DISTINCT SEQ FROM CTE_1) AS A

SET @SQL='
WITH CTE
AS
(SELECT B.*,A.PURCHASE_DATE,A.PURCHASER,
CASE WHEN B.INVENTORY>=SUB_TOTAL THEN A.AMOUNT
     WHEN B.INVENTORY<SUB_TOTAL AND B.INVENTORY>=SUB_TOTAL-A.AMOUNT THEN AMOUNT-(SUB_TOTAL-INVENTORY)
	 ELSE 0 END AS APPORTION
FROM #A A
JOIN #B B ON A.SKU=B.SKU
OUTER APPLY (SELECT SUM(AMOUNT) AS SUB_TOTAL FROM #A WHERE A.SKU=SKU AND PURCHASE_DATE>=A.PURCHASE_DATE) AS C),

CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY PURCHASE_DATE) AS SEQ FROM CTE
WHERE APPORTION<>0)

SELECT SKU,'+@SQL+' FROM CTE_1 GROUP BY SKU'

EXEC(@SQL)

[weixin_44460901]

感謝2位的回復