FIFO Implementation in Inventory using SQL
As some comment already said a CTE can solve this
with cte as (
select item, wh, stock_in, stock_out, price, value
, row_number() over (partition by item, wh order by item, wh) as rank
from myTable)
select a.item, a.wh
, a.stock_in - coalesce(b.stock_out, 0) stock
, a.price
, a.value - coalesce(b.value, 0) value
from cte a
left join cte b on a.item = b.item and a.wh = b.wh and a.rank = b.rank - 1
where a.stock_in - coalesce(b.stock_out, 0) > 0
If the second "Item B" has the wrong price (the IN price is 25, the OUT is 35).
SQL 2008 fiddle
Just for fun, with sql server 2012 and the introduction of the LEAD and LAG function the same thing is possible in a somewhat easier way
with cte as (
select item, wh, stock_in
, coalesce(LEAD(stock_out)
OVER (partition by item, wh order by item, wh), 0) stock_out
, price, value
, coalesce(LEAD(value)
OVER (partition by item, wh order by item, wh), 0) value_out
from myTable)
select item
, wh
, (stock_in - stock_out) stock
, price
, (value - value_out) value
from cte
where (stock_in - stock_out) > 0
SQL2012 fiddle
Update
ATTENTION -> To use the two query before this point the data need to be in the correct order.
To have the details with more then one row per day you need something reliable to order the row with the same date, like a date column with time, an autoincremental ID or something down the same line, and it's not possible to use the query already written because they are based on the position of the data.
A better idea is to split the data in IN and OUT, order it by item, wh and data, and apply a rank on both data, like this:
SELECT d_in.item
, d_in.wh
, d_in.stock_in - coalesce(d_out.stock_out, 0) stock
, d_in.price
, d_in.value - coalesce(d_out.value, 0) value
FROM (SELECT item, wh, stock_in, price, value
, rank = row_number() OVER
(PARTITION BY item, wh ORDER BY item, wh, date)
FROM myTable
WHERE stock_out = 0) d_in
LEFT JOIN
(SELECT item, wh, stock_out, price, value
, rank = row_number() OVER
(PARTITION BY item, wh ORDER BY item, wh, date)
FROM myTable
WHERE stock_in = 0) d_out
ON d_in.item = d_out.item AND d_in.wh = d_out.wh
AND d_in.rank = d_out.rank
WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0
SQLFiddle
But this query is NOT completely reliable, the order of data in the same order group is not stable.
I haven't change the query to recalculate the price if the IN.price is different from the OUT.price
How to implement FIFO in sql
This should work for you:
Working sample on Fiddle
CREATE FUNCTION [dbo].[GetBatchAmounts]
(
@requestedAmount int
)
RETURNS
@tBatchResults TABLE
(
Batch nvarchar(50),
Amount int
)
AS
BEGIN
/*This is just a mock of ersults of your query*/
DECLARE @RS_GIN_Master TABLE(
Qty int,
batch_no NVARCHAR(max),
accept_date DATETIME
)
insert into @RS_GIN_Master(Qty,batch_no,accept_date)
SELECT 10,'BT002', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(4 AS varchar) AS DATETIME)
insert into @RS_GIN_Master(Qty,batch_no,accept_date)
SELECT 10,'BT003', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(5 AS varchar) AS DATETIME)
insert into @RS_GIN_Master(Qty,batch_no,accept_date)
SELECT 10,'BT001', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(6 AS varchar) AS DATETIME)
/*---------------------------*/
DECLARE @Qty int
DECLARE @batch_no NVARCHAR(max)
DECLARE @accept_date DATETIME
DECLARE myCursor CURSOR FOR
SELECT Qty, batch_no, accept_date FROM @RS_GIN_Master ORDER BY accept_date ASC
OPEN myCursor
FETCH NEXT FROM myCursor INTO @Qty, @batch_no,@accept_date
WHILE (@@FETCH_STATUS = 0 AND @requestedAmount > 0 )
BEGIN
Declare @actualQty int
IF @requestedAmount > @Qty
SET @actualQty = @Qty
ELSE
SET @actualQty = @requestedAmount
INSERT INTO @tBatchResults (batch, Amount)
SELECT @batch_no, @actualQty
set @requestedAmount = @requestedAmount - @actualQty
FETCH NEXT FROM myCursor INTO @Qty, @batch_no,@accept_date
END /*WHILE*/
CLOSE myCursor
DEALLOCATE myCursor
RETURN
END
Just make sure to replace the marked part of the function with your query...
Fifo Calculation Using SQL
May be this will solve your problem. If Not, Please explain your scenario more detail
Before Going to Actual Implementation Refer the links, How to Loop through the set of records and Cursor
Sample Data for Inventory Table:
Query For FIFO Basis
Declare @ReqOrderQty as int;
Set @ReqOrderQty=45; // Requested Qty to Update the Records
Declare
@ItemNo Varchar(10),
@MFGCode Varchar(10),
@StockQty int,
@OrderQty int;
Declare @Query Varchar(500);
Declare Records Cursor
for Select ItemNo,MFGCode,StockQty,OrderQty from Inventory where ItemNo='1' and OrderQty <> StockQty order by MFGCode asc
OPEN Records
FETCH NEXT FROM Records INTO
@ItemNo,
@MFGCode,
@StockQty,
@OrderQty;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ReqOrderQty > @StockQty
BEGIN
Set @ReqOrderQty = @ReqOrderQty - @StockQty;
Set @Query='Update Inventory set OrderQty=' +CAST(@StockQty as varchar(100))+' where ItemNo='''+@ItemNo +'''and MFGCode='''+@MFGCode+''''
END
Else
BEGIN
Set @ReqOrderQty = @ReqOrderQty % @StockQty;
Set @Query='Update Inventory set OrderQty=' +CAST(@ReqOrderQty as varchar(100))+' where ItemNo='''+@ItemNo +'''and MFGCode='''+@MFGCode+''''
END
PRINT @Query
Exec (@Query)
FETCH NEXT FROM Records INTO
@ItemNo,
@MFGCode,
@StockQty,
@OrderQty;
END;
CLOSE Records;
DEALLOCATE Records;
Output
String Split
create table #Temp(value varchar(10))
Declare @ReqOrderQty Varchar(200)
Set @ReqOrderQty = '200,40,10,100,150';
INSERT INTO #Temp SELECT * FROM STRING_SPLIT ( @ReqOrderQty , ',' )
// Perform the Cursor Operation as mentioned above
Drop Table #Temp
Accept the Answer, If it helps you
FIFO stock inventory valuation
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
FIFO Inventory Aging
After some trial and error this implementation is giving me the desired results:
CREATE TABLE #LINE ([Cusip] VARCHAR(32),
[TradingActivity] VARCHAR(10),
[NotionalAmount] DECIMAL (20,2),
[TradeDate] DATE,
[ValDate] DATE)
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-01-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-01-12','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-07-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-07-10','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-10-07','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-10-08','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-11-06','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-11-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-12-06','2016-01-07')
INSERT INTO #LINE VALUES('T0001_A','BUY','1000000.00','2015-12-07','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-01-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-01-12','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-07-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-07-10','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-10-07','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-10-08','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-11-06','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-11-09','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-12-06','2016-01-07')
INSERT INTO #LINE VALUES('T0001_B','SELL','1000000.00','2015-12-07','2016-01-07')
INSERT INTO #LINE VALUES('T0002','BUY','2000000.00','2015-01-09','2016-01-07')
INSERT INTO #LINE VALUES('T0002','SELL','1000000.00','2015-01-09','2016-01-07')
INSERT INTO #LINE VALUES('T0003','SELL','2000000.00','2015-07-09','2016-01-07')
INSERT INTO #LINE VALUES('T0003','BUY','1000000.00','2015-07-09','2016-01-07')
INSERT INTO #LINE VALUES('T0004','BUY','1000000.00','2015-10-07','2016-01-07')
INSERT INTO #LINE VALUES('T0004','SELL','2000000.00','2015-10-07','2016-01-07')
INSERT INTO #LINE VALUES('T0005','SELL','1000000.00','2015-11-06','2016-01-07')
INSERT INTO #LINE VALUES('T0005','BUY','2000000.00','2015-11-06','2016-01-07')
INSERT INTO #LINE VALUES('T0006','BUY','1000000.00','2015-11-07','2016-01-07')
INSERT INTO #LINE VALUES('T0006','SELL','1000000.00','2015-11-07','2016-01-07')
INSERT INTO #LINE VALUES('T0007','SELL','2000000.00','2015-12-07','2016-01-07')
INSERT INTO #LINE VALUES('T0007','BUY','2000000.00','2015-12-07','2016-01-07')
INSERT INTO #LINE VALUES('T0008','SELL','1000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0008','BUY','2000000.00','2015-01-08','2016-01-07')
INSERT INTO #LINE VALUES('T0009','BUY','1000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0009','SELL','2000000.00','2015-07-09','2016-01-07')
INSERT INTO #LINE VALUES('T0010','SELL','2000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0010','BUY','1000000.00','2015-10-07','2016-01-07')
INSERT INTO #LINE VALUES('T0011','BUY','2000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0011','SELL','1000000.00','2015-11-06','2016-01-07')
INSERT INTO #LINE VALUES('T0012','SELL','1000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0012','BUY','1000000.00','2015-11-07','2016-01-07')
INSERT INTO #LINE VALUES('T0013','SELL','2000000.00','2015-12-07','2016-01-07')
INSERT INTO #LINE VALUES('T0013','BUY','2000000.00','2016-01-07','2016-01-07')
INSERT INTO #LINE VALUES('T0014','SELL','2000000.00','2015-12-03','2016-01-07')
INSERT INTO #LINE VALUES('T0014','BUY','1000000.00','2015-01-01','2016-01-07')
INSERT INTO #LINE VALUES('T0014','SELL','2000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0014','BUY','2000000.00','2015-10-02','2016-01-07')
INSERT INTO #LINE VALUES('T0015B','SELL','1000000.00','2015-12-03','2016-01-07')
INSERT INTO #LINE VALUES('T0015A','BUY','4000000.00','2015-04-02','2016-01-07')
INSERT INTO #LINE VALUES('T0015A','SELL','3000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0015B','BUY','2000000.00','2015-10-01','2016-01-07')
INSERT INTO #LINE VALUES('T0016B','SELL','2000000.00','2015-12-01','2016-01-07')
INSERT INTO #LINE VALUES('T0016A','BUY','3000000.00','2015-04-02','2016-01-07')
INSERT INTO #LINE VALUES('T0016A','SELL','4000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0016B','BUY','1000000.00','2015-10-01','2016-01-07')
INSERT INTO #LINE VALUES('T0017A','SELL','3000000.00','2015-12-09','2016-01-07')
INSERT INTO #LINE VALUES('T0017B','SELL','2000000.00','2015-12-09','2016-01-07')
INSERT INTO #LINE VALUES('T0017A','BUY','4000000.00','2015-10-09','2016-01-07')
INSERT INTO #LINE VALUES('T0017B','BUY','1000000.00','2015-10-09','2016-01-07')
INSERT INTO #LINE VALUES('T0018B','SELL','2000000.00','2015-12-01','2016-01-07')
INSERT INTO #LINE VALUES('T0018A','BUY','4000000.00','2015-04-02','2016-01-07')
INSERT INTO #LINE VALUES('T0018B','BUY','1000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0018A','SELL','3000000.00','2015-10-01','2016-01-07')
INSERT INTO #LINE VALUES('T0019B','BUY','8000000.00','2015-12-03','2016-01-07')
INSERT INTO #LINE VALUES('T0019A','SELL','1000000.00','2015-01-01','2016-01-07')
INSERT INTO #LINE VALUES('T0019B','BUY','5000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0019A','SELL','7000000.00','2015-09-02','2016-01-07')
INSERT INTO #LINE VALUES('T0020B','SELL','8000000.00','2015-12-03','2016-01-07')
INSERT INTO #LINE VALUES('T0020A','SELL','1000000.00','2015-01-01','2016-01-07')
INSERT INTO #LINE VALUES('T0020B','BUY','5000000.00','2015-07-02','2016-01-07')
INSERT INTO #LINE VALUES('T0020A','BUY','7000000.00','2015-09-02','2016-01-07')
-- Assign LOT numbers to each record based on CUSIP, TradingActivity, and Bucket. And assign rankings to the buckets used for ordering later on.
IF OBJECT_ID('tempdb..#RollUp') IS NOT NULL
DROP TABLE #RollUp;
SELECT A.[Cusip],
A.[TradingActivity],
A.[NotionalAmount],
[LOT] =ROW_NUMBER() OVER (PARTITION BY A.[Cusip],A.[TradingActivity],CASE WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) BETWEEN 0 AND 30 THEN '0' --'[=<30]'
WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) BETWEEN 31 AND 60 THEN '1' --'[31-60]'
WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) BETWEEN 61 AND 90 THEN '2' --'[61-90]'
WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) BETWEEN 91 AND 180 THEN '3' --'[91-180]'
WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) BETWEEN 181 AND 360 THEN '4' --'[181-360]'
WHEN DATEDIFF(day,A.[TradeDate],A.[ValDate]) > 360 THEN '5' --'[+360]'
END ORDER BY A.[TradeDate] )
,
[Bucket]= CASE WHEN DATEDIFF(day,[TradeDate],[ValDate]) BETWEEN 0 AND 30 THEN '0' --'[=<30]'
WHEN DATEDIFF(day,[TradeDate],[ValDate]) BETWEEN 31 AND 60 THEN '1' --'[31-60]'
WHEN DATEDIFF(day,[TradeDate],[ValDate]) BETWEEN 61 AND 90 THEN '2' --'[61-90]'
WHEN DATEDIFF(day,[TradeDate],[ValDate]) BETWEEN 91 AND 180 THEN '3' --'[91-180]'
WHEN DATEDIFF(day,[TradeDate],[ValDate]) BETWEEN 181 AND 360 THEN '4' --'[181-360]'
WHEN DATEDIFF(day,[TradeDate],[ValDate]) > 360 THEN '5' --'[+360]'
END INTO #RollUp
FROM #Line AS A
ORDER BY [Cusip],[TradeDate]
-- Apply (+/-) sign to NotionalAmount and derive the SUM of NotionalAmount based on CUSIP, TradingActivity, and Bucket
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP;
SELECT A.[Cusip],
A.[Bucket],
A.[LOT],
A.[NotionalAmount],
[SignedNotionalAmount] = CASE WHEN A.[TradingActivity] = 'BUY' THEN A.[NotionalAmount] ELSE 0-A.[NotionalAmount] END,
A.[TradingActivity],
[SumNotionalAmount] = SUM(A.[NotionalAmount])OVER(PARTITION BY A.[Cusip],A.[TradingActivity], A.[Bucket] ) INTO #TEMP
FROM #RollUp AS A
ORDER BY A.[Bucket] DESC,
A.[LOT] ASC
-- Rank the notional ammounts for each CUSIP based on TradeActivity
IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL
DROP TABLE #TEMP2;
SELECT A.[Cusip],
A.[Bucket],
[SumNotionalAmount] = SUM(A.[SignedNotionalAmount]),
[SignedNotionalAmountRanked] = RANK() OVER( PARTITION BY A.[Cusip],CASE WHEN SUM(A.[SignedNotionalAmount]) > 0 THEN 'BUY'
ELSE 'SELL' END
ORDER BY A.Bucket DESC) INTO #TEMP2
FROM #TEMP AS A
GROUP BY A.[Cusip],
A.[Bucket]
-- Calculate Sum of the Notional amount based on FIFO algorithm at CUSIP level and place in correct bucket(s)
IF OBJECT_ID('tempdb..#TEMP3') IS NOT NULL
DROP TABLE #TEMP3;
SELECT A.[Cusip], A.[Bucket] AS ABucket,B.[Bucket] AS BBucket, A.[SumNotionalAmount] AS ASumNotionalAmount , B.[SumNotionalAmount] AS BSumNotionalAmount,
[Bucket] = CASE WHEN ABS(A.[SumNotionalAmount])>ABS(B.[SumNotionalAmount]) THEN CASE A.[Bucket] WHEN 0 THEN '=<30'
WHEN 1 THEN '31-60'
WHEN 2 THEN '61-90'
WHEN 3 THEN '91-180'
WHEN 4 THEN '181-360'
WHEN 5 THEN '+360'
END
WHEN ABS(A.[SumNotionalAmount])<ABS(B.[SumNotionalAmount]) THEN CASE B.[Bucket] WHEN 0 THEN '=<30'
WHEN 1 THEN '31-60'
WHEN 2 THEN '61-90'
WHEN 3 THEN '91-180'
WHEN 4 THEN '181-360'
WHEN 5 THEN '+360'
END
WHEN (ABS(A.[SumNotionalAmount])=ABS(B.[SumNotionalAmount]) AND A.[SumNotionalAmount]>B.[SumNotionalAmount]) THEN CASE B.[Bucket] WHEN 0 THEN '=<30'
WHEN 1 THEN '31-60'
WHEN 2 THEN '61-90'
WHEN 3 THEN '91-180'
WHEN 4 THEN '181-360'
WHEN 5 THEN '+360'
END
WHEN (ABS(A.[SumNotionalAmount])=ABS(B.[SumNotionalAmount]) AND A.[SumNotionalAmount]<B.[SumNotionalAmount]) THEN CASE A.[Bucket] WHEN 0 THEN '=<30'
WHEN 1 THEN '31-60'
WHEN 2 THEN '61-90'
WHEN 3 THEN '91-180'
WHEN 4 THEN '181-360'
WHEN 5 THEN '+360'
END
ELSE CASE A.[Bucket] WHEN 0 THEN '=<30'
WHEN 1 THEN '31-60'
WHEN 2 THEN '61-90'
WHEN 3 THEN '91-180'
WHEN 4 THEN '181-360'
WHEN 5 THEN '+360'
END
END ,
[RunningSumNotionalAmount] = SUM(A.[SumNotionalAmount]+B.[SumNotionalAmount] )OVER(PARTITION BY A.[Cusip] )
,
[SumNotionalAmount] = CASE WHEN B.[Bucket] IS NOT NULL THEN A.[SumNotionalAmount]+B.[SumNotionalAmount]
ELSE A.SumNotionalAmount
END INTO #TEMP3
FROM #TEMP2 AS A LEFT OUTER JOIN
#TEMP2 AS B ON A.[Cusip] = B.[Cusip] AND
A.[SignedNotionalAmountRanked] = B.[SignedNotionalAmountRanked] AND
A.[Bucket] <> B.[Bucket]
WHERE (A.Bucket > B.Bucket OR
B.Bucket IS NULL)
ORDER BY A.Cusip,
A.Bucket
IF OBJECT_ID('tempdb..#TEMP4') IS NOT NULL
DROP TABLE #TEMP4;
SELECT *, (SELECT SUM(i.[SumNotionalAmount])
FROM #TEMP3 AS i
WHERE i.[Cusip] = s.[Cusip] AND
i.[Bucket] < s.[Bucket]
) AS RollingStock , HasRemainder = ABS([RunningSumNotionalAmount]) + s.[SumNotionalAmount] INTO #TEMP4
FROM #TEMP3 AS s
SELECT [Cusip],
[=<30],
[31-60],
[61-90],
[91-180],
[181-360],
[+360]
FROM
( SELECT [Cusip],
[Bucket],
[SumNotionalAmount] = CASE WHEN SUM([RollingStock]+ [HasRemainder] )OVER(PARTITION BY [Cusip] ) IS NOT NULL AND
[HasRemainder] IS NOT NULL THEN ISNULL([RollingStock],0)
ELSE [SumNotionalAmount]
END
FROM #TEMP4 ) p
PIVOT
( SUM([SumNotionalAmount] )
FOR [Bucket] IN ( [=<30],
[31-60],
[61-90],
[91-180],
[181-360],
[+360] )
) AS pvt
The results:
FIFO SQL Query for stock and value
I got what I expected ,
declare @Stock table (item char(3) not null,[date] date not null,[time] time not null,in_qty decimal(18,5) null,out_qty decimal(18,5) null,in_Price decimal(10,2) null,out_Price decimal(10,2) null)
insert into @Stock(item , [date] ,[time] , in_qty, out_qty, in_Price,out_Price) values
('ABC','20120401','00:00:00',200 ,0 ,750 ,0),
('ABC','20120401','00:00:00',0 ,100 ,0 ,800),
('ABC','20120401','00:00:00',50 ,0 ,700 ,0),
('ABC','20120401','00:00:00',75 ,0 ,800 ,0),
('ABC','20120401','00:00:00',0 ,175 ,0 ,850),
('XYZ','20120401','00:00:00',150 ,0 ,350 ,0),
('XYZ','20120401','00:00:00',0 ,120 ,0 ,380),
('XYZ','20120401','00:00:00',0 ,80 ,0 ,370),
('XYZ','20120401','00:00:00',80 ,0 ,330 ,0),
('XYZ','20120401','00:00:00',90 ,0 ,340 ,0),
('PQR','20120401','00:00:00',70 ,0 ,240 ,0),
('PQR','20120401','00:00:00',30 ,0 ,230 ,0)
;WITH OrderedIn as
(
select *,ROW_NUMBER()OVER (PARTITION BY item ORDER BY date asc,time asc) as S_No
from @Stock
where in_qty <> 0
), RunningTotals as
(
select item, in_qty, in_Price as price, cast(in_qty as varchar(100)) as Total
,cast(0 as varchar(100)) as PrevTotal,S_No from OrderedIn where S_No = 1
union all
select rt.item ,oi.in_qty ,oi.in_Price as price
,cast(rt.Total + oi.in_qty as varchar(100)),cast(rt.Total as varchar(100)),oi.S_No
from
RunningTotals rt
inner join OrderedIn oi
on rt.item = oi.item
and rt.S_No = oi.S_No - 1
), TotalOut as
(
select item,SUM(out_qty) as Qty from @Stock where out_Price <> 0 group by item
)
select
rt.item
,SUM(CASE WHEN PrevTotal > COALESCE(out.Qty,0) THEN rt.in_qty ELSE rt.Total - COALESCE(out.Qty,0) END) as Qty
,SUM(CASE WHEN PrevTotal > COALESCE(out.Qty,0) THEN rt.in_qty ELSE rt.Total - COALESCE(out.Qty,0) END * (price)) as Value
from
RunningTotals rt
left join
TotalOut out
on
rt.item = out.item
where
rt.Total > COALESCE(out.Qty,0)
group by rt.item
The first observation is that we don't need to do anything special for OUT transactions - we just need to know the total quantity. That's what the TotalOut CTE calculates. The first two CTEs work with IN transactions, and compute what "interval" of stock each represents - change the final query to just select * from RunningTotals to get a feel for that.
If you have separate columns for date and time ,Then you should group by date first then time.
- In FIFO (First in first out) valuation Group by for date and time like ascending
FIFO inventory systems - Converting T-SQL to Linq
I was able to resolve this
void Main()
{
var data = new List<History>()
{
new History(1,1,20,DateTime.Now.AddDays(-24),"001"),
new History(2,1,2,DateTime.Now.AddDays(-23),"002"),
new History(3,2,2,DateTime.Now.AddDays(-24),"001"),
new History(3,1,29,DateTime.Now.AddDays(-22),"003"),
new History(3,1,50,DateTime.Now.AddDays(-21),"004"),
};
var demo = Results(data, 30);
demo.Dump(); //note using LinqPad
}
public class History
{
public History(int id, int stockId, int qty, DateTime date, string batchNumber)
{
Id = id;
StockId = stockId;
Qty = qty;
Date = date;
BatchNumber = batchNumber;
}
public int Id { get; set; }
public int StockId { get; set; }
public int Qty { get; set; }
public string BatchNumber { get; set; }
public DateTime Date { get; set; }
}
public static List<Result> Results(List<History> data, int takenQty)
{
var runningTotal = 0;
var result = data.Where(p => p.StockId == 1).OrderBy(p => p.Date).ThenBy(p => p.Id)
.Select(x => new
{
x.Id,
x.Date,
x.BatchNumber,
x.Qty,
x.StockId,
CumQty = (runningTotal = runningTotal + x.Qty)
}).ToList();
var query = result.Select(x => new Result
{
StockId =x.StockId,
Id = x.Id,
BatchNumber = x.BatchNumber,
Qty = x.Qty,
Used = x.CumQty < takenQty ? x.Qty : takenQty - (x.CumQty - x.Qty)
}).Take((result.Count(p => p.CumQty < takenQty)) + 1).ToList();
return query;
}
public class Result
{
public int Id { get; set; }
public int StockId { get; set; }
public int Qty { get; set; }
public string BatchNumber { get; set; }
public int Used { get; set; }
public int Left => Qty - Used;
}
And the final output 
Related Topics
Sql-Server: Error - Exclusive Access Could Not Be Obtained Because the Database Is in Use
Is There Any Difference Between "!=" and "<>" in Oracle SQL
Using SQL Count in a Case Statement
How to Load SQL Fixture in Django for User Model
Bulk Insert, SQL Server 2000, Unix Linebreaks
How to Search All Text Fields in a Db for Some Substring with T-Sql
How Does Subquery in Select Statement Work in Oracle
SQL Count* Group by Bigger Than,
How to Create an "On-The-Fly" Mapping Table Within a Select Statement in Postgresql
Identity_Insert Is Set to Off - How to Turn It On
New Line Issue When Copying Data from SQL Server 2012 to Excel
Does the Order of Columns Matter in a Group by Clause
Selecting Entries by Date - >= Now(), MySQL
Sqlite: Preventing Duplicate Rows