First In, First Out，FIFO 是以最早購入的存貨成本作為損益表中的主營業務成本，後購入的存貨成本作為資產負載表中的存貨計價的會計計算方法。
會要計算這個其實是因為有成本報表的關係，一般來說，這類的計算方式都在ERP的系統中已經幫你完成。
不過有些ERP會使用移動平均成本來做為計算方式，然而，FIFO的優勢在於存貨的價值會更貼近市場目前價值。
到底這是怎麼算的呢??
| 日期 | 數量 | 價格 | 進出貨 | 金額 | 累計成本 | 說明 |
| 5/1 | 100 | 10 | IN | 100*10=1000 | 1000 | 進貨花費1000 |
| 5/3 | 30 | 0 | OUT | 30*10=300 | 700 | 銷貨1000-300 |
| 5/5 | 50 | 12 | IN | 50*12=600 | 1300 | 進貨700+600 |
| 5/6 | 40 | 0 | OUT | 40*10=400 | 900 |
前次存貨未銷完 100-30-40=30 30*10+12*50=900 |
當不是一進一銷時，整個的計算方式就會變得複雜，原先採用的想法是利用CURSOR (指標)來進行迴圈的計算。
反正就是從期初開始取得資料，進貨就加，銷貨就減.....就這樣資料就跑到天荒地老，報表轉轉轉.....
這時就想，如果可以將已經銷售歸零的資料排除，那需要算的就少啦........想破頭都不知道怎麼將歸零的資料排除(因為不是一進一銷)。
最後 發現有批牛肉很便宜 ，參考了國外網站的解法，利用反查方式取得成本資料，在這裡實作一下，有興趣的朋友請參閱連結。
解決方式大致如下：
- 取得目前的剩餘庫存數量【進貨數量-銷貨數量】
- 取得反向的進貨累計數量，也就是從最後一次進貨開始往回累加數量。
- 因為按照FIFO，剩餘的數量一定是後面幾次進貨的剩餘。所以只要找到【累加庫存數量 >= 剩餘庫存】的那筆進貨資料，再往下推算即可。(這樣期初就可以完全不用管阿.....大心....)
- 取得【累加庫存數量 >= 剩餘庫存】的那筆資料、並算出此筆【進貨的剩餘數量】。
- EX：進貨70個，10天後剩50個，【進貨剩餘數量】= 50
- 最後將每筆資料的【進貨數量 * 該筆進貨單價】與【進貨剩餘數量 * 該筆進貨單價】加總起來，就是成本嚕!!。
直接看範例吧!! 可以直接改成CTE方式查詢
--驗證資料版本 使用 TEMP資料夾進行
--步驟1 建立測試資料
CREATE TABLE #dt
(
SN int --流水編號
,PID varchar(10) --商品代碼
,TranDate smalldatetime --交易日期
,TranCode char(3) --交易種類
,Qty int
,Price decimal(10,3)
,CurrentQty int --累計庫存(檢核用)
,CurrentValue decimal(10,3) --累計成本(檢核用)
)
INSERT INTO #dt VALUES
('8395' ,'11715', '2009-01-03 07:05:00','IN ', 724 , 62.840 ,724 , 45496.160 ),
('84826' ,'11715', '2009-01-24 01:44:00','OUT', 66 , 0.000 ,658 , 41348.720 ),
('107290' ,'11715', '2009-01-30 05:04:00','OUT', 604 , 0.000 ,54 ,3393.360 ),
('157117' ,'11715', '2009-02-12 17:55:00','OUT', 11 , 0.000 ,43 ,2702.120 ),
('164409' ,'11715', '2009-02-14 18:07:00','OUT', 24 , 0.000 ,19 ,1193.960 ),
('179199' ,'11715', '2009-02-18 17:23:00','OUT', 5 , 0.000 ,14 ,879.760 ),
('183978' ,'11715', '2009-02-20 00:39:00','OUT', 7 , 0.000 ,7 , 439.880 ),
('189935' ,'11715', '2009-02-21 16:09:00','IN ', 330 , 301.580 ,337 , 99961.280 ),
('205633' ,'11715', '2009-02-25 22:45:00','RET', 79 , 0.000 ,416 , 123786.100),
('221981' ,'11715', '2009-03-02 10:22:00','RET', 385 , 0.000 ,801 , 239894.400),
('222217' ,'11715', '2009-03-02 11:52:00','RET', 422 , 0.000 ,1223 ,367161.160 ),
('247427' ,'11715', '2009-03-09 09:27:00','RET', 234 , 0.000 ,1457 ,437730.880 ),
('252892' ,'11715', '2009-03-10 21:24:00','RET', 504 , 0.000 ,1961 ,589727.200 ),
('254377' ,'11715', '2009-03-11 07:48:00','OUT', 1958, 0.000 ,3 , 904.740 ),
('278233' ,'11715', '2009-03-17 18:45:00','IN ', 856 , 77.310 ,859 , 67082.100 ),
('283039' ,'11715', '2009-03-19 02:47:00','OUT', 209 , 0.000 ,650 , 50251.500 ),
('324187' ,'11715', '2009-03-30 09:34:00','OUT', 84 , 0.000 ,566 , 43757.460 ),
('330668' ,'11715', '2009-04-01 04:04:00','OUT', 453 , 0.000 ,113 , 8736.030 ),
('330895' ,'11715', '2009-04-01 05:37:00','RET', 113 , 0.000 ,226 , 17472.060 ),
('360798' ,'11715', '2009-04-09 10:14:00','OUT', 16 , 0.000 ,210 , 16235.100 ),
('368072' ,'11715', '2009-04-11 09:20:00','IN ', 1153, 195.160 ,1363 ,241254.580 ),
('407793' ,'11715', '2009-04-22 04:32:00','IN ', 707 , 69.710 ,2070 ,290539.550 ),
('466548' ,'11715', '2009-05-08 04:50:00','RET', 1997, 0.000 ,4067 ,429750.420 ),
('468535' ,'11715', '2009-05-08 17:36:00','OUT', 2618, 0.000 ,1449 ,101009.790 ),
('472029' ,'11715', '2009-05-09 15:57:00','OUT', 434 , 0.000 ,1015 ,70755.650 ),
('485692' ,'11715', '2009-05-13 10:45:00','RET', 405 , 0.000 ,1420 ,98988.200 ),
('526653' ,'11715', '2009-05-24 14:54:00','RET', 43 , 0.000 ,1463 ,101985.730 ),
('541905' ,'11715', '2009-05-28 18:25:00','OUT', 509 , 0.000 ,954 , 66503.340 ),
('554691' ,'11715', '2009-06-01 06:43:00','OUT', 398 , 0.000 ,556 , 38758.760 ),
('571343' ,'11715', '2009-06-05 19:33:00','RET', 13 , 0.000 ,569 , 39664.990 ),
('579926' ,'11715', '2009-06-08 04:23:00','OUT', 358 , 0.000 ,211 , 14708.810 ),
('589210' ,'11715', '2009-06-10 17:57:00','OUT', 48 , 0.000 ,163 , 11362.730 ),
('593665' ,'11715', '2009-06-11 23:02:00','OUT', 160 , 0.000 ,3 , 209.130 ),
('612032' ,'11715', '2009-06-17 00:19:00','IN ', 1176, 398.500 ,1179 ,468845.130 ),
('614271' ,'11715', '2009-06-17 15:33:00','OUT', 473 , 0.000 ,706 , 281341.000),
('625608' ,'11715', '2009-06-20 18:20:00','RET', 148 , 0.000 ,854 , 340319.000),
('640324' ,'11715', '2009-06-24 19:12:00','IN ', 954 , 188.150 ,1808 ,519814.100 ),
('659796' ,'11715', '2009-06-30 04:09:00','OUT', 1249, 0.000 ,559 , 105175.850),
('663600' ,'11715', '2009-07-01 05:11:00','OUT', 409 , 0.000 ,150 , 28222.500 ),
('665516' ,'11715', '2009-07-01 17:46:00','RET', 89 , 0.000 ,239 , 44967.850 ),
('666589' ,'11715', '2009-07-02 00:30:00','IN ', 835 , 206.880 ,1074 ,217712.650 ),
('691520' ,'11715', '2009-07-08 19:34:00','OUT', 184 , 0.000 ,890 , 183093.050),
('699846' ,'11715', '2009-07-11 01:01:00','OUT', 570 , 0.000 ,320 , 66201.600 ),
('702186' ,'11715', '2009-07-11 16:34:00','OUT', 245 , 0.000 ,75 ,15516.000 ),
('704135' ,'11715', '2009-07-12 05:20:00','RET', 75 , 0.000 ,150 , 31032.000 ),
('745749' ,'11715', '2009-07-23 14:20:00','OUT', 133 , 0.000 ,17 ,3516.960 ),
('750426' ,'11715', '2009-07-24 21:31:00','OUT', 10 , 0.000 ,7 , 1448.160 ),
('751500' ,'11715', '2009-07-25 04:27:00','IN ', 1101, 172.080 ,1108 ,190908.240 ),
('794865' ,'11715', '2009-08-05 23:59:00','RET', 107 , 0.000 ,1215 ,209320.800 ),
('809924' ,'11715', '2009-08-10 03:45:00','OUT', 984 , 0.000 ,231 , 39750.480 ),
('823333' ,'11715', '2009-08-13 18:04:00','OUT', 201 , 0.000 ,30 ,5162.400 ),
('852038' ,'11715', '2009-08-21 13:31:00','OUT', 5 , 0.000 ,25 ,4302.000 ),
('880026' ,'11715', '2009-08-29 06:10:00','OUT', 8 , 0.000 ,17 ,2925.360 ),
('880617' ,'11715', '2009-08-29 10:10:00','OUT', 17 , 0.000 ,0 , 0.000 ),
('883691' ,'11715', '2009-08-30 06:29:00','IN ', 802 , 121.040 ,802 , 97074.080 ),
('912160' ,'11715', '2009-09-06 23:54:00','IN ', 1076, 385.420 ,1878 ,511786.000 ),
('918874' ,'11715', '2009-09-08 19:05:00','OUT', 637 , 0.000 ,1241 ,434683.520 ),
('924378' ,'11715', '2009-09-10 06:34:00','OUT', 54 , 0.000 ,1187 ,428147.360 ),
('933640' ,'11715', '2009-09-12 18:47:00','RET', 456 , 0.000 ,1643 ,603898.880 ),
('950559' ,'11715', '2009-09-17 10:25:00','IN ', 607 , 290.750 ,2250 ,780384.130 ),
('967366' ,'11715', '2009-09-22 01:35:00','OUT', 903 , 0.000 ,1347 ,461696.050 ),
('993285' ,'11715', '2009-09-29 03:47:00','OUT', 484 , 0.000 ,863 , 275152.770)
--建立索引，加速查詢
CREATE NONCLUSTERED INDEX IX_Dave_General
ON #dt
( PID ASC,
TranDate DESC,
TranCode ASC
)
INCLUDE ( Qty, Price);
GO
CREATE NONCLUSTERED INDEX IX_Dave_Qty
ON #dt
( PID ASC,
TranDate ASC )
INCLUDE (Qty)
WHERE (TranCode IN('IN','RET'));
CREATE NONCLUSTERED INDEX IX_Dave_Price
ON #dt
( PID ASC,
TranDate ASC
)
INCLUDE ( Price)
WHERE (TranCode='IN');
GO
--步驟2
--取得庫存數量
SELECT PID ,
SUM(CASE WHEN TranCode ='OUT' THEN 0 - Qty ELSE Qty END) AS TotalStock
INTO #ctePIDsum
FROM #dt
WHERE PID = '11715'
GROUP BY PID
--SELECT * FROM #ctePIDsum
--步驟3
--取得反向的進貨累計數量
SELECT s.SN,
--ROW_NUMBER() OVER(ORDER BY s.TranDate DESC) AS NU,
s.PID,
s.TranDate,
( SELECT SUM(i.Qty)
FROM #dt AS i WITH( INDEX( IX_Dave_Qty) )
WHERE i.PID = s.PID AND i.TranCode IN ( 'IN','RET' ) AND i.TranDate >= s.TranDate
) AS RollingQty ,
s.Qty AS ThisQty
INTO #cteReverseInSum
FROM #dt AS s
WHERE s.TranCode IN ( 'IN','RET' ) AND PID = '11715'
--SELECT * FROM #cteReverseInSum --ORDER BY TranDate
--步驟4
--取得最後交易日期的資料、【累加庫存數量 >= 剩餘庫存】的那筆資料、並算出此筆【進貨的剩餘數量】。
SELECT
w.PID,
w.TotalStock, --剩餘數量
LastP.TranDate, --交易日期
LastP.StockToUse, --本次數量
LastP.TotalQty, -- 累計總數
w.TotalStock - LastP.TotalQty + LastP.StockToUse AS UseThisQty, --=此筆進貨的剩餘數量
LastP.SN
INTO #cteLastDate
FROM #ctePIDsum AS w
CROSS APPLY
( SELECT TOP (1) z.SN, z.TranDate,z.ThisQty AS StockToUse ,z.RollingQty AS TotalQty
FROM #cteReverseInSum AS z
WHERE z.PID= w.PID AND z.RollingQty>= w.TotalStock
AND z.PID = '11715'
ORDER BY z.TranDate DESC
) AS LastP
--SELECT * FROM #cteLastDate
--步驟5
--計算該品項的庫存數量與金額
SELECT y.PID,
y.TotalStock AS CurrentQty ,
SUM(CASE WHEN e.TranDate = y.TranDate THEN y.UseThisQty ELSE e.Qty END * Price.Price )AS CurrentValue
FROM #cteLastDate AS y
INNER JOIN #dt AS e WITH( INDEX( IX_Dave_Qty) )
ON e.PID= y.PID
AND e.TranDate >= y.TranDate
AND e.TranCode IN ( 'IN','RET' )
CROSS APPLY (
SELECT TOP( 1 ) p.Price FROM #dt AS p WITH (INDEX ( IX_Dave_Price ) )
WHERE p.PID = e.PID AND p.TranDate <= e.TranDate AND p.TranCode = 'IN'
ORDER BY p.TranDate DESC ) AS Price
GROUP BY y.PID , y.TotalStock
ORDER BY y.PID;
SELECT * FROM #dt
SELECT * FROM #ctePIDsum
SELECT * FROM #cteReverseInSum
SELECT * FROM #cteLastDate
--刪除暫存資料
DROP TABLE #dt
DROP TABLE #ctePIDsum
DROP TABLE #cteReverseInSum
DROP TABLE #cteLastDate
DROP INDEX
IX_Dave_General ON #dt ,
IX_Dave_Qty ON #dt,
IX_Dave_Price ON #dt
後記：當部分資料都查詢出來後，延伸去查詢其他的進貨資料就容易了，但是若要用日期下去做判斷條件，建議還是要再帶一個可查詢鍵，做成key，避免時間條件相同時，合併查詢出現錯誤。
水滴可成涓流，涓流可成湖泊大海。
汲取累積知識，將知識堆積成常識；將常識探究成學識；將學識簡化為知識；授人自省。