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 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 can I manage a FIFO-queue in an database with SQL?
Reading the comments you say that you are willing to add a auto increment or date field to know the proper position of each row. Once you add this I would recommend adding one more row to the In table called processed which is automatically set to false when the row is added to the table. Any rows that have been copied to OUT already have their processed filed set to true.
+----+
| In |
+-----------+-----------+-------+-----------+
| AUtoId | Supply_ID | Price | Processed |
+-----------+-----------+-------+-----------+
| 1 | 1 | 75 | 1 |
| 2 | 1 | 75 | 1 |
| 3 | 1 | 75 | 0 |
| 4 | 2 | 80 | 0 |
| 5 | 2 | 80 | 0 |
+-----------+-----------+-------+---------- +
Then to find the next item to move to OUT you can do
SELECT TOP 1 Supply_ID, Price
FROM In WHERE Processed = 0
ORDER BY [Your Auto Increment Field or Date]
Once the row is moved to OUT then you just UPDATE the processed field of that row to true.
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
sql query for fifo inventory
I think a simple check on sales.qty < purchase.qty won't work. Since even if you have sales.qty < purchase.qty but have leftovers from last day then you will be using those leftovers first.
You should use try this:
with cte as(
select s.id,s.Sid,sDate,s.Productid,s.qty AS Qty,s.qty as saleqty,p.qty as productqty,p.price
,sum(p.qty-s.qty) over (order by sdate) as leftover
from purchase P
inner join sales S
on p.productid=s.productid
and p.pdate=s.sdate
)
select id, Sid,sDate,Productid,Qty,
case when lag(leftover) over (order by sdate)>0 then lag(leftover *price) over( order by sdate)
+( saleqty-lag(leftover) over (order by sdate)) * price
else saleqty * price end as profit
from cte;
SQL FIFO inventory query for aging a dynamic number of units
declare @amount_to_deduct int = 50
SELECT *,
case when cumulative = 0 or units < 0 then 0
when cumulative < @amount_to_deduct and cumulative < units then cumulative
when cumulative < @amount_to_deduct and cumulative >= units then units
when cumulative - units < @amount_to_deduct and cumulative < units then @amount_to_deduct
when cumulative - units < @amount_to_deduct and cumulative >= units then @amount_to_deduct - cumulative + units
else 0
end as selected
FROM (
SELECT id, date, units, IIF(cumulative > 0, cumulative, 0) cumulative
FROM (
SELECT id, date, units, SUM(units) OVER (ORDER BY type, date) AS cumulative FROM transactions
) a
) b
ORDER BY date
Here's a sql fiddle with some extra testing data: http://sqlfiddle.com/#!18/b0584/10
I tested with cases of 50, 30, 100, and 150 using that sample and the data looked to be correct
How can I write a Postgres (SQL) query for FIFO 'closing stock' inventory valuation?
Using "Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem" as an example, re-working that approach for Postgres and the change of table/columns produces this query:
/* Sum up the ins and outs to calculate the remaining stock level */
WITH cteStockSum
AS ( SELECT sku ,
SUM(adjustment_quantity) AS TotalStock
FROM mockup_inv_movements
GROUP BY sku
)
, cteReverseInSum
AS ( SELECT s.sku ,
s.created_at ,
( SELECT SUM(i.adjustment_quantity)
FROM mockup_inv_movements AS i
WHERE i.sku = s.sku
AND i.adjustment_quantity > 0
AND i.created_at >= s.created_at
) AS RollingStock ,
s.adjustment_quantity AS ThisStock
FROM mockup_inv_movements AS s
WHERE s.adjustment_quantity > 0
)
/* Using the rolling balance above find the first stock movement in that meets
(or exceeds) our required stock level */
/* and calculate how much stock is required from the earliest stock in */
, cteWithLastTranDate
AS ( SELECT w.sku ,
w.TotalStock ,
LastPartialStock.created_at ,
LastPartialStock.StockToUse ,
LastPartialStock.RunningTotal ,
w.TotalStock - LastPartialStock.RunningTotal
+ LastPartialStock.StockToUse AS UseThisStock
FROM cteStockSum AS w
CROSS JOIN LATERAL ( SELECT
z.created_at ,
z.ThisStock AS StockToUse ,
z.RollingStock AS RunningTotal
FROM cteReverseInSum AS z
WHERE z.sku = w.sku
AND z.RollingStock >= w.TotalStock
ORDER BY z.created_at DESC
LIMIT 1
) AS LastPartialStock
)
/* Sum up the cost of 100% of the stock movements in after the returned stockid and for that stockid we need 'UseThisStock' items' */
SELECT y.sku ,
y.TotalStock AS CurrentItems ,
SUM(CASE WHEN e.created_at = y.created_at THEN y.UseThisStock
ELSE e.adjustment_quantity
END * Price.unit_cost) AS CurrentValue
FROM cteWithLastTranDate AS y
INNER JOIN mockup_inv_movements AS e
ON e.SKU = y.SKU
AND e.created_at >= y.created_at
AND e.adjustment_quantity > 0
CROSS JOIN LATERAL (
/* Find the Price of the item in */ SELECT
p.unit_cost
FROM mockup_inv_movements AS p
WHERE p.SKU = e.SKU
AND p.created_at <= e.created_at
AND p.adjustment_quantity > 0
ORDER BY p.created_at DESC
LIMIT 1
) AS Price
GROUP BY y.sku ,y.TotalStock
ORDER BY y.sku
and from your sample data the result produced is this:
+-------------+--------------+--------------+
| sku | currentitems | currentvalue |
+-------------+--------------+--------------+
| bar_product | 75 | 545.00 |
| foo_product | 25 | 155.00 |
+-------------+--------------+--------------+
also see: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f564a6cfda3374c2057b437f845a4bdf
Related Topics
How to Subtract 2 Dates in Oracle to Get the Result in Hour and Minute
How to Group by and Return Sum Row in Postgres
Using Different Order by with Union
Retrieve Rank from SQLite Table
Sqlite Get Name of Attached Databases
Splitting Variable Length Delimited String Across Multiple Rows (Sql)
How to Create a Temporary Table from a Type
How to Make Comment Reply Query in MySQL
Error (Ora-00923: from Keyword Not Found Where Expected)
What Is the Equivalent of Regexp_Substr in MySQL
How to Run SQL Server Stored Procedures in Parallel
Postgresql Gin Index Slower Than Gist for Pg_Trgm
What Is the Equivalent of Xml Path and Stuff in Linq Lambda Expression (Group_Concat/String_Agg)