SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

Here is the sample data

FIFO sample SQL 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
Code

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

FIFO query in SQL Server

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.