Skip to Content
0
Oct 14 at 12:35 PM

how to aggregate data based on condition in SQL

75 Views Last edit Oct 14 at 12:37 PM 3 rev

Hi,

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) .

raw data

|item|creation date|issue date|qty|open quantity|

|----|-------------|----------|---|-------------|

|a|05.01.2021|10.01.2021|2|0|

|a|07.01.2021|12.02.2021|3|2|

|a|11.01.2021|12.02.2021|4|3|

|b|05.01.2021|10.05.2021|2|0|

|b|11.01.2021|12.05.2021|3|2|

|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 

Attachments

table-items.jpg (38.9 kB)