库存先入先出_存储过程求助(2)
刚一个帖子我举例的有错,重新开一帖子
有一张库存表t1 主要字段为
入库单编号 物料名称 入库时间 供应商 入库数量 入库单价
------------------------------------------------
win001 物料001 2013-1-1 供应商A 200 10
win002 物料001 2013-1-2 供应商A 100 10
win003 物料002 2013-1-5 供应商B 50 20
win004 物料002 2013-1-7 供应商B 100 25
(同样物料入库价格可能不一样)
出库表2
出库单编号 物料名称 出库时间 客户 出库数量 出库单价
----------------------------------------------
XOUT001 物料001 2013-1-3 客户X 100 22
XOUT002 物料001 2013-1-4 客户X 20 22
XOUT003 物料002 2013-1-10 客户y 60 20
XOUT004 物料002 2013-1-10 客户Z 30 20
XOUT005 物料001 2013-1-11 客户y 25 20
(同样物料出库价格可能不一样)
我想以先进先出原则计算入库单的出库情况,具体查询主要结果如如下
入库单编号 物料名称 入库时间 入库数量 客户 出库数量
------------------------------------------------
win001 物料001 2013-1-1 200 客户X 120(100+20 合并)
win001 物料001 2013-1-1 200 客户y 25
win002 物料001 2013-1-2 100 无 0
win003 物料002 2013-1-5 50 客户y 50
win004 物料002 2013-1-7 100 客户y 10
win004 物料002 2013-1-7 100 客户z 30
请问这个查询怎么写?
[解决办法]
最后结果里面，入库单编号和出库数据量没法做关联，因为出库的时候只知道物料名称，不知道入库单编号，理顺逻辑才好写sql啊
[解决办法]
-- 建立测试数据
WITH StockIn (InNo, Item, Supplier, Amount, InDate) AS (
SELECT 'win001', 'Item001', 'Supplier001', 200, '01/01/2013'
UNION ALL
SELECT 'win002', 'Item001', 'Supplier001', 100, '01/02/2013'
UNION ALL
SELECT 'win003', 'Item002', 'Supplier002', 50, '01/05/2013'
UNION ALL
SELECT 'win004', 'Item002', 'Supplier002', 80, '01/07/2013'
UNION ALL
SELECT 'win005', 'Item002', 'Supplier003', 150, '01/09/2013'
UNION ALL
SELECT 'win006', 'Item003', 'Supplier003', 100, '01/12/2013'
)
SELECT * INTO #StockIn FROM StockIn;
WITH StockOut (OutNo, Item, Client, Amount, OutDate) AS (
SELECT 'wout001', 'Item001', 'Client001', 100, '01/03/2013'
UNION ALL
SELECT 'wout002', 'Item001', 'Client001', 20, '01/04/2013'
UNION ALL
SELECT 'wout003', 'Item002', 'Client002', 60, '01/10/2013'
UNION ALL
SELECT 'wout004', 'Item002', 'Client001', 30, '01/11/2013'
UNION ALL
SELECT 'wout005', 'Item001', 'Client003', 25, '01/11/2013'
UNION ALL
SELECT 'wout006', 'Item002', 'Client003', 45, '01/13/2013'
)
SELECT * INTO #StockOut FROM StockOut
-- 测试数据建立完毕
-- 查询
SELECT ag.InNo, ag.Item, ag.InDate, ag.InAmount, ag.Client, OutAmountPerClient=SUM(ag.OutAmount)
FROM (
SELECT InNo=i.InNo, Item=i.Item, InDate=i.InDate, InAmount=i.Amount, ISNULL(ip.PrevSum,0) AS InPrevSum, ic.CurrSum AS InCurrSum, Client=
CASE WHEN o.Client IS NULL THEN ''
WHEN ISNULL(ip.PrevSum,0) >= oc.CurrSum THEN ''
WHEN ic.CurrSum <=ISNULL(op.PrevSum,0) THEN ''
ELSE o.Client END,
OutAmount=CASE WHEN o.Client IS NULL THEN 0
WHEN ISNULL(ip.PrevSum,0) >= oc.CurrSum THEN 0
WHEN ic.CurrSum <=ISNULL(op.PrevSum,0) THEN 0
ELSE CASE
WHEN ic.CurrSum >=oc.CurrSum THEN CASE WHEN ISNULL(ip.PrevSum,0) < oc.CurrSum AND ISNULL(ip.PrevSum,0)> ISNULL(op.PrevSum,0) THEN oc.CurrSum - ISNULL(ip.PrevSum,0) ELSE o.Amount END
ELSE ic.CurrSum - ISNULL(op.PrevSum,0)
END
END,
OutDate=o.OutDate, ISNULL(op.PrevSum,0) AS OutPrevSum , oc.CurrSum AS OutCurrSum, StockApplication = CASE
WHEN o.Client IS NULL THEN 2 -- 没出库单
WHEN ISNULL(ip.PrevSum,0) >= oc.CurrSum THEN 0 -- 旧库存足够供应所有出库单， 现入库单没有消耗
WHEN ic.CurrSum <=ISNULL(op.PrevSum,0) THEN 0 -- 库存已消耗完毕， 现出库单没有实现
ELSE 1 END -- 应用现入库单给当前出库单
FROM #StockIn i LEFT JOIN #StockOut o ON i.Item = o.Item AND i.InDate < o.OutDate
CROSS APPLY (SELECT SUM(s.Amount) AS PrevSum FROM #StockIn s WHERE s.Item=i.Item AND s.InNo<>i.InNo AND s.InDate<=i.InDate) ip
CROSS APPLY (SELECT SUM(s.Amount) AS CurrSum FROM #StockIn s WHERE s.Item=i.Item AND s.InDate<=i.InDate) ic
CROSS APPLY (SELECT SUM(s.Amount) AS PrevSum FROM #StockOut s WHERE s.Item=o.Item AND s.OutNo<>o.OutNo AND s.OutDate<=o.OutDate) op
CROSS APPLY (SELECT SUM(s.Amount) AS CurrSum FROM #StockOut s WHERE s.Item=o.Item AND s.OutDate<=o.OutDate) oc
) ag
WHERE ag.StockApplication > 0
GROUP BY ag.InNo, ag.Item, ag.InDate, ag.InAmount, ag.Client, ag.StockApplication
DROP TABLE #StockIn
DROP TABLE #StockOut
