FIFO stock inventory valuation

Question

i am using sql server 2014..My stock transaction table is like this.

declare @Stock table (Item char(3) not null,[Date] datetime not null,TxnType varchar(3) not null,Qty int not null,Price decimal(10,2) null)
insert into @Stock(Item ,  [Date] ,        TxnType, Qty,  Price) values
('ABC','20120401','IN',    200, 750.00),
('ABC','20120405','OUT',   100 ,null  ),
('ABC','20120410','IN',     50, 700.00),
('ABC','20120416','IN',     75, 800.00),
('ABC','20120425','OUT',   175, null  ),
('XYZ','20120402','IN',    150, 350.00),
('XYZ','20120408','OUT',   120 ,null  ),
('XYZ','20120412','OUT',    10 ,null  ),
('XYZ','20120424','IN',     90, 340.00);

I need to calculate below 2 cases at every monthe end.

Stock valuation of the remaining quantity

OUTPUT :

Stock valuation of the stock data

Item  Qty      Value
ABC   50    40000.00
XYZ   110   37400.00

Please help me to get the solution in FIFO

You seem to have multiple questions, which makes it less likely that anyone will answer. — Gordon Linoff, Jan 25 '17 at 01:51
Do you _have_ to do this in SQL? SQL is designed to read and write data. Complex rules like this are much more suitable for an application layer, — D Stanley, Jan 25 '17 at 01:57
Possible duplicate of [FIFO Implementation in Inventory using SQL](http://stackoverflow.com/questions/22936112/fifo-implementation-in-inventory-using-sql) — DeanOC, Jan 25 '17 at 02:35
@DeanOC I don't believe it is a duplicate because this requires not only a partition based on the column Item but Sum (adding/subtracting) based on the value of another column. Where as that post only Sums up two different columns values together. — Edward, Jan 25 '17 at 04:47
This was a learning experience for me on how to do a case within a Sum aggregation. — Edward, Jan 25 '17 at 04:49
Thanks @ Edward for yor answer...is it possible to same can achieved using window function?pls let me know — Ajt, Jan 25 '17 at 05:04

Edward · Accepted Answer · 2017-01-25T04:51:07.397

1

Select Item, FinalQty as [Final Qty], CurrPrice*FinalQty as [Current Value] from (
 select Item, Sum(Case When TxnType='OUT' Then -Qty Else Qty End) as FinalQty ,
        (Select Top 1 Price from @Stock where Price is not null and s.Item=Item order by Date Desc) as CurrPrice
 from @Stock s
 group by Item ) n

edited Jan 25 '17 at 04:51

answered Jan 25 '17 at 04:44

Edward

864
1
7
29

@Thanks..it is working fine..if posible pls give a solution using window functions – Ajt Jan 25 '17 at 05:15
An function (like sum) with the over (partition by...) is considered windowing. which is what I tried doing but that cannot have conditions pressed on it like in your case with the IN adds and OUT subtracts. Look here [Windowing by 'Over'](https://msdn.microsoft.com/en-us/library/ms189461.aspx?f=255&MSPPError=-2147217396) – Edward Jan 25 '17 at 05:16

FIFO stock inventory valuation

1 Answers1