how to aggregate data based on condition

I would like to calculate the open quantity per item via comparing the creation and issue date in SQL , if the issue date is less than creation date on other rows for same item, quantity should be added to the open quantity

for example row 3 has open quantity 3 because item a created on 11.01.2021 and at that time only row 2 with issue date 12.02.2021 is still open. Row 1 is not added to open quantity in row 3 because it is already issued on 10.01.2021 .
for the 6th row for item b, row 4 and row 5 are not issued on the creation date of row 6 (14.02.2021) therefore open quantity is 2+3 (row 4 qty+ row 5 qty) .

I know it is not correct but if it would be possible I would add a code as below.

select item, createdate, issuedate, qty 
sum(qty) OVER(PARTITION BY item  where createdate_issuedate_aggrow < createdate_referencerow < issuedate_aggrow   )  from t 

Solution 1:

This is a reoccurring pattern. If you want compare a table with itself and build conditional aggregates, you need to use a self-join combined with an aggregation. This is not only true for SAP HANA, but a general SQL pattern.

From that perspective your question is very similar to that one: How do i use STRING_AGG in combination with WHERE clause?

The following statement will give you the desired result and works similarly on any database system.

Sample Data

create table sample_Data (item, creation_date, issue_date, qty) as (
select 'a', to_date('05.01.2021', 'DD.MM.YYYY'), to_date('10.01.2021', 'DD.MM.YYYY'), 2 from dummy union all
select 'a', to_date('07.01.2021', 'DD.MM.YYYY'), to_date('12.02.2021', 'DD.MM.YYYY'), 3 from dummy union all
select 'a', to_date('11.01.2021', 'DD.MM.YYYY'), to_date('12.02.2021', 'DD.MM.YYYY'), 4 from dummy union all
select 'b', to_date('05.01.2021', 'DD.MM.YYYY'), to_date('10.05.2021', 'DD.MM.YYYY'), 2 from dummy union all
select 'b', to_date('11.01.2021', 'DD.MM.YYYY'), to_date('12.05.2021', 'DD.MM.YYYY'), 3 from dummy union all
select 'b', to_date('14.02.2021', 'DD.MM.YYYY'), to_date('15.02.2021', 'DD.MM.YYYY'), 4 from dummy
) 
;

Query

SELECT new_record.*, ifnull(sum(open_record.qty),0) AS open_quantity
FROM sample_data new_record
LEFT JOIN sample_data open_record
    ON new_record.item = open_record.item 
    AND open_record.issue_date > new_record.creation_date
    AND open_record.creation_date < new_record.creation_date
GROUP BY new_record.item, new_record.creation_date, new_record.issue_date, new_record.qty
ORDER BY new_record.item, new_record.creation_date, new_record.issue_date

Result