how to aggregate data based on condition in SQL

[erkanakin]

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 

[DotEiserman · Accepted solution]

Hi Emil,

You can use a windows function in combination with a case, I would try that, for e.g. your code snippet:

select item, createdate, issuedate, qty ,

sum(qty) OVER(PARTITION BY item where createdate_issuedate_aggrow < createdate_referencerow < issuedate_aggrow ) from t

you can do the case before the sum i.e.

select item, createdate, issuedate, qty,

case when createdate_issuedate_aggrow < createdate_referencerow

and createdate_referencerow < issuedate_aggrow then sum(qty) OVER (PARTITION BY item ) else 0 end as "OpenQuantity" from t

Let me know.

Thanks

Dot

[ThorstenHoefer]

Hi,

I assume, you mean the issue date of the sum should be after the creation date,
hence that the issue date is still open?

sumtab~issue > current~create

select current~item
     , current~creation
     , current~issue 
     , current~qty
     , sum(sumtab~qty) as open
from table as current
left outer join table as sumtab
  on current~item = sumtab~item
 and sumtab~issue  > current~create 
 and sumtab~create < current~create
 group by current~item
        , current~creation
        , current~issue 
        , current~qty