I would like to calculate the open quantity per item via comparing the creation and issue date in HANA SQL (and willing to use the code in table functions, and afterwards the same in calculation view). 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) .
|item|creation date|issue date|qty|open quantity|
|b|14.02.2021|15.02.2021|4|2+3 = 5|
I know it is not correct but if it would be possible I would add a code as
select item, createdate, issuedate, qty sum(qty) OVER(PARTITION BY item where createdate_issuedate_aggrow < createdate_referencerow < issuedate_aggrow ) from t