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 SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




CRM Companies List
Web Based CRM Software


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 query for stock movements for calculating aging on SAP HANA database


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.

calculate net quantity using SUM() and CASE using SQLScript


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.

running sum using SQL for SAP HANA database developer


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.

SAP HANA database SQL codes to calculate stock aging

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

SQLScript codes for stock aging on SAP HANA database for SQL programmer

I hope SQL programmers are happy with the SQLScript query although it seems to be complex providing a solution in single CTE SELECT query.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems