FIFO Example Query in SQL Server
This SQL tutorial includes FIFO example query in SQL Server. SQL FIFO method example query is based on two database tables SalesOrder and ProductionOrder sql tables. SQL Server SalesOrder table stores sales orders for produced products coming from customers. To provide ordered products, internal production orders are created in the system. These production orders are stored in ProductionOrder database table.
When a production order is inserted in database table, according to the FIFO method the first sales order created for the corresponding product is fullfiled partially or totally. This SQL tutorial provides a Transact-SQL query to return remaining product quantity for a sales order according to FIFO (First In First Out) basis. FIFO solution in this SQL Server includes CTE (Common Table Expression) queries.
Here is the SQL script required to create database tables SalesOrder and ProductionOrder in SQL Server. Also the SQL script generate sample data in these two database tables.
Create Table SalesOrder (
OrderId varchar(10),
OrderDate datetime,
ProductId varchar(10),
OrderQty int
)
Create Table ProductionOrder (
OrderId varchar(10),
OrderDate datetime,
ProductId varchar(10),
OrderQty int
)
insert into SalesOrder select 'SO-0001', '20120105 13:45', 'PROD-01', 50
insert into SalesOrder select 'SO-0002', '20120108 12:00', 'PROD-02', 40
insert into SalesOrder select 'SO-0003', '20120109 10:30', 'PROD-01', 20
insert into SalesOrder select 'SO-0004', '20120110 17:10', 'PROD-03', 30
insert into ProductionOrder select 'PO-0001','20120115 15:00','PROD-01',30
insert into ProductionOrder select 'PO-0002','20120115 18:00','PROD-02',20
insert into ProductionOrder select 'PO-0003','20120116 18:00','PROD-01',30
select * from SalesOrder --order by ProductId
select * from ProductionOrder --order by ProductId
Here is the sample data
The following CTE query returns the list of sales orders and open quantity whose order quantity is not covered by production orders. The WHERE clause where "LeftQty > 0" is applied filters the orders with open quantities.
; with s as (
select
*,
SoldUpToNow = (
select sum(OrderQty) from SalesOrder
where ProductId = s.ProductId and OrderDate <= s.OrderDate
)
from SalesOrder s
), p as (
select ProductId, sum(OrderQty) as TotalProduced
from ProductionOrder
group by ProductId
)
select * from (
select
s.*,
p.TotalProduced,
case
when s.SoldUpToNow - isnull(p.TotalProduced,0) < 0 then 0
when (s.SoldUpToNow - isnull(p.TotalProduced,0) ) > s.OrderQty then s.OrderQty
else s.SoldUpToNow - isnull(p.TotalProduced,0)
end as LeftQty
from s
left join p on s.ProductId = p.ProductId
) fifo
where LeftQty > 0
The first SQL CTE query s returns the sales orders and an additional cumulative order quantity column. The cumulative order quantity column SoldUpToNow shows total number of orders given for that product up to that order date.
The second SQL CTE query p returns the total number of production orders for each product.
The main SELECT statement which joins two CTE expressions returns the sales order, cumulative order amount, and open order quantity based on FIFO method
This SQL query solution can also be applied to stock movements in a warehouse or in inventory management. Sample data tables in this FIFO example problem, can be replaced with stock movements of a range of products into the warehouse and out from warehouse. Transfer orders can be used as transactions for stock movements.