Inventory Ageing Calculation view in Hana Studio

Question

I want to make a calculation view in hana studio where I have to maintain ageing of product which is pending in Inventory.

It will subtract on first come first basis...I mean in case of Product P001 then 5500 will subtract 1000 first then balance quantity will subtract 2000 and so on until it will be zero.. Below is the transaction table..

Department  |Product  | Date               | Quantity  |Indicator
------------+---------+--------------------+-----------+---------
D001        |P001     | 01-Jul-2017        | 1000      |ADD
D001        |P001     | 10-Jul-2017        | 2000      |ADD
D001        |P001     | 15-Jul-2017        | 3000      |ADD
D001        |P001     | 16-Jul-2017        | 2000      |ADD
D001        |P001     | 18-Jul-2017        | 5500      |SUBTRACT
D001        |P002     | 12-Jul-2017        | 3000      |ADD
D001        |P002     | 14-Jul-2017        | 2000      |ADD
D001        |P002     | 15-Jul-2017        | 4000      |SUBTRACT

And the final output should be something like. if today's date is 31-Jul-2017

Department  |Product  | Date               | Quantity  |AgeinginDays
------------+---------+--------------------+-----------+---------
D001        |P001     | 15-Jul-2017        | 500       |16
D001        |P001     | 16-Jul-2017        | 2000      |15
D001        |P002     | 14-Jul-2017        | 1000      |17

score 0 · Answer 1 · answered Nov 28 '17 at 07:56

I created the Inventory table and populated it with your sample data. Here is the SQL codes for that. Please try to share such meta data when you are asking help in future :)

create column table InventoryItems
(
Department varchar(10),
Product varchar(10),
Date date,
Quantity int,
Indicator varchar(10)
);
insert into InventoryItems select 'D001','P001','20170701',1000,'ADD' from dummy;
insert into InventoryItems select 'D001','P001','20170710',2000,'ADD' from dummy;
insert into InventoryItems select 'D001','P001','20170715',3000,'ADD' from dummy;
insert into InventoryItems select 'D001','P001','20170716',2000,'ADD' from dummy;
insert into InventoryItems select 'D001','P001','20170718',5500,'SUBTRACT' from dummy;
insert into InventoryItems select 'D001','P002','20170712',3000,'ADD' from dummy;
insert into InventoryItems select 'D001','P002','20170714',2000,'ADD' from dummy;
insert into InventoryItems select 'D001','P002','20170715',4000,'SUBTRACT' from dummy;

After sample table and data is ready, I prepared following SQLScript code for aging. Since it maybe difficult to understand and follow the code, I shared your problem and provided a detailed solution at stock aging SQL calculation on SAP HANA database

And here is the SQL codes to help you fulfill your requirement

with Ins as (
    select 
        row_number() over (partition by Product order by date) as AddId,
        *,
        sum(Quantity) over (partition by Product order by Date rows unbounded preceding) as sumIn,
        sum(Quantity) over (partition by Product order by Date desc rows unbounded preceding) as sumX
    from InventoryItems 
    where 
        Indicator = 'ADD'
), Outs as (
    select 
        row_number() over (partition by Product order by date) as AddId,
        *,
        sum(Quantity) over (partition by Product order by Date rows unbounded preceding) as sumOut
    from InventoryItems 
    where 
        Indicator = 'SUBTRACT'
), Inv as (
    select
        ins.Product, max(sumIn) - max(ifnull(sumOut,0)) as inv
    from Ins
    left join Outs
        on ins.Product = outs.Product
    group by ins.Product
), calc as (
    select
        Ins.*,
        Inv.inv,
        inv-sumx as diff
    from Ins
    left join Inv 
        on ins.Product = inv.Product
)
select
    Department, Product, Date, 
    case when diff > 0 then quantity else quantity-abs(diff) end as Quantity,
    DAYS_BETWEEN(Date,current_date) as AgeingInDays
from (
    select *, 1 as rn from calc where diff >= 0
    union all
    (
        select * from (
            select 
                *, row_number() over (partition by Product order by diff desc) as rn 
            from calc 
            where diff < 0
        ) t where rn = 1
    )
)
order by Product, AddId

And this is the output of the above SQL block execution

I hope it helps,

Inventory Ageing Calculation view in Hana Studio

1 Answers1