Stock Aging SQL Calculation Sample on SAP HANA Database
On SAP HANA database using SQLScript, SQL programmers can calculate stock aging based on FIFO (First In First Out) valuation method as shown in this tutorial. For SAP developers, to summarize the case solved in this HANA database SQLScript tutorial is as follows: Stock items or products are accepted into stock areas, plants via inbound movement types. These stock movement transactions are stored in a HANA database table. In this SAP table, outbound movement types or stock out transactions are also stored.
Using SQLScript features on HANA database, SAP programmers can solve stock aging problem easily.
Multiple CTE expressions are used, running sum calculations with SUM() aggregation function is used, row_number() function and CASE expression is used, and days_between() datetime function is used to calculate inventory aging or stock aging on sample HANA database tables.
SAP HANA Database Sample Table and Data for Stock Aging Example
SQLScript developers can create following column based database tables on development SAP HANA database by executing the DDL commands for table creation.
Then I also provided some sample data for each table. We are dealing with StockMovements table especially.
Other database tables are lookup tables for definitions of product (or inventory items) and plants (warehouses, etc)
SQL programmers will be executing SQLScript codes on these tables for stock aging or inventory aging calculations given in this database tutorial.
create column table Product
(
ProductId int,
Product varchar(100)
);
insert into Product values (1,'Product01');
insert into Product values (2,'Product02');
insert into Product values (3,'Product03');
insert into Product values (4,'Product04');
insert into Product values (5,'Product05');
create column table Plant
(
PlantId int,
Plant varchar(100)
);
insert into Plant values (1,'PlantA');
insert into Plant values (2,'PlantB');
insert into Plant values (3,'PlantC');
create column table StockMovements
(
PlantId int,
ProductId int,
MovementDate date,
Quantity int,
MovementType varchar(10)
);
insert into StockMovements values (1,1,'20170115',100,'IN');
insert into StockMovements values (2,1,'20170131',50,'IN');
insert into StockMovements values (3,2,'20170201',10,'IN');
insert into StockMovements values (1,1,'20170210',50,'OUT');
insert into StockMovements values (2,1,'20170218',15,'OUT');
insert into StockMovements values (1,1,'20170225',10,'IN');
insert into StockMovements values (3,2,'20170228',10,'IN');
insert into StockMovements values (1,1,'20170311',30,'OUT');
SQLScript Query for Inbound and Outbound Stock Movements
Inbound or outbound inventory movements based on StockMovements table data can be summarized by running following SQLScript SELECT statement on HANA database.
select
row_number() over (order by MovementDate) as Id,
m.PlantId,
p.Plant,
m.ProductId,
u.Product,
MovementDate,
MovementType,
Quantity
from StockMovements as m
inner join Plant as p on p.PlantId = m.PlantId
inner join Product as u on u.ProductId = m.ProductId
The output of the SQL query can be seen in below screenshot.
I want to show SAP HANA database developers how they can calculate inventory aging of products on stock places (in this example plants) using SQLScript in this tutorial.
For simplicity and to understand how SQLScript query works, I will not calculate product aging based on plants seperately, but as a single stock place for all products.
SQL Code for Net Quantity after Stock Movements
First of all, developers can calculate the resultant net amounts after inbound and outbound stock movements for each product by using following SQLScript SELECT statement where SQL CTE expression is used.
SQL CTE expressions are basically sub-select statements that can be referred more than once in a SQLScript SELECT statements.
Note for SQLScript database developer; SQL CTE stands for Common Table Expression.
with movements as (
select
row_number() over (order by MovementDate) as Id,
-- m.PlantId,
-- p.Plant,
m.ProductId,
u.Product,
MovementDate,
MovementType,
Quantity
from StockMovements as m
inner join Plant as p on p.PlantId = m.PlantId
inner join Product as u on u.ProductId = m.ProductId
)
select
ProductId,
sum( case when MovementType = 'IN' then Quantity else -1 * Quantity end ) as inv
from movements
group by ProductId;
When above SQLScript CTE query is executed, database developers can report the last status of product quantities in stock. Here is our inventory after all in-and-out movements are considered with SUM() aggregation function per product.
Running Sum for Stock using Rows Unbounded Preceding Clause
As the next step, I will seperate stock movements according to their direction as IN for inbound or OUT for outbound stock movements using MovementType field as seperate CTE expressions.
Check following SQLScript query. Pay attention to especially where SUM() aggregate function is used.
SUM() with Partition By clause enables SQL programmers to calculate sum on quantity columns for each different partition column combination, so for ProductId in this case.
Addition of ROWS UNBOUNDED PRECEDING to Partition By clause in SUM() aggregation function makes calculating running sum easy for HANA database developers.
with movements as
(
select
row_number() over (order by MovementDate) as Id,
-- m.PlantId,
-- p.Plant,
m.ProductId,
u.Product,
MovementDate,
MovementType,
Quantity
from StockMovements as m
inner join Plant as p on p.PlantId = m.PlantId
inner join Product as u on u.ProductId = m.ProductId
)
select
row_number() over (partition by ProductId order by MovementDate) as AddId,
*,
sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumIn,
sum(Quantity) over (partition by ProductId order by MovementDate desc rows unbounded preceding) as sumInDesc
from movements
where
MovementType = 'IN'
order by
ProductId, MovementDate;
Let's execute the above SQLScript code block and check the output
As I marked on the screenshot, there is two running sum calculations provided by above SQL statement. One is running up with date, the other is increasing from the most recent date through the first date of the stock movement.
Calculate Stock Aging on HANA Database
Following HANA database SQL script joins calculations for net quantity, inbound stock movements and output stock movements in one SQLScript query.
with movements as (
select
row_number() over (order by MovementDate) as Id,
-- m.PlantId,
-- p.Plant,
m.ProductId,
u.Product,
MovementDate,
MovementType,
Quantity
from StockMovements as m
inner join Plant as p on p.PlantId = m.PlantId
inner join Product as u on u.ProductId = m.ProductId
), net as (
select
ProductId,
sum( case when MovementType = 'IN' then Quantity else -1 * Quantity end ) as net
from movements
group by ProductId
), inputs as (
select
row_number() over (partition by ProductId order by MovementDate) as AddId,
*,
sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumIn,
sum(Quantity) over (partition by ProductId order by MovementDate desc rows unbounded preceding) as sumInDesc
from movements
where
MovementType = 'IN'
), outputs as (
select
row_number() over (partition by ProductId order by MovementDate) as AddId,
*,
sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumOut
from movements
where
MovementType = 'OUT'
)
select
inputs.*,
net.net,
net.net - inputs.sumInDesc as diff
from inputs
left join net
on inputs.ProductId = net.ProductId
SAP HANA database programmers can especially concentrate on last 3 columns; SumInDesc(ending), Net and Diff(erence)
NET column value is same for all rows of the same product showing the current resultant quantity after all stock movements.
SumInDesc(ending) field is for running sum of quantities in reverse order, so to calculate aging of stocks it is better to move from last stock movement to first one.
Diff(erence) field value is calculated simply by using formula Net-SumInDesc
When Diff is positive, it means products from related stock movement record exists in the stock right now.
So an aging calculation should be done for that specific inbound stock movement. This is important.
If Diff is negative, it figures out that all stock from related inbound stock movement is already used for following outbound stock movements.
There is no need for stock aging calculations for that line.
Here is the final SQLScript code SELECT statement that can be used for stock aging using FIFO (First In First Out) algorithm on SAP HANA database.
with movements as (
select
row_number() over (order by MovementDate) as Id,
-- m.PlantId,
-- p.Plant,
m.ProductId,
u.Product,
MovementDate,
MovementType,
Quantity
from StockMovements as m
inner join Plant as p on p.PlantId = m.PlantId
inner join Product as u on u.ProductId = m.ProductId
), net as (
select
ProductId,
sum( case when MovementType = 'IN' then Quantity else -1 * Quantity end ) as net
from movements
group by ProductId
), inputs as (
select
row_number() over (partition by ProductId order by MovementDate) as AddId,
*,
sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumIn,
sum(Quantity) over (partition by ProductId order by MovementDate desc rows unbounded preceding) as sumInDesc
from movements
where
MovementType = 'IN'
), outputs as (
select
row_number() over (partition by ProductId order by MovementDate) as AddId,
*,
sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumOut
from movements
where
MovementType = 'OUT'
), calculation as (
select
inputs.*,
net.net,
net.net - inputs.sumInDesc as diff
from inputs
left join net
on inputs.ProductId = net.ProductId
)
select
ProductId, Product, MovementDate, -- *,
case when diff > 0 then quantity else quantity-abs(diff) end as InStockQuantity,
days_between(MovementDate, current_date) as AgeingInDays
from (
select *, 1 as rn from calculation where diff >= 0
union all
(
select * from (
select
*, row_number() over (partition by ProductId order by diff desc) as rn
from calculation
where diff < 0
) t where rn = 1
)
) as temptable
order by ProductId, AddId
I hope SQL programmers are happy with the SQLScript query although it seems to be complex providing a solution in single CTE SELECT query.
