库存先入先出_存储过程求助(2)

Question

刚一个帖子我举例的有错,重新开一帖子有一张库存表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请问这个查询怎么写?

Answer 1

最后结果里面，入库单编号和出库数据量没法做关联，因为出库的时候只知道物料名称，不知道入库单编号，理顺逻辑才好写sql啊

Answer 2

-- 建立测试数据
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