how to aggregate data based on condition

Question

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

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

And also show us your current query attempt. And add a tag for the dbms you're using. — jarlh, Oct 13 '21 at 08:38
I try to write it in SAP HANA SQL , I tried to do it with group by statement but it didn't work — erkan, Oct 13 '21 at 08:48
@erkan: *"I tried to do it with group by statement"* thats what jarlh asked to share in the question — huMpty duMpty, Oct 13 '21 at 08:52
@huMptyduMpty I've added the logic of calculation a bit detailed to the question — erkan, Oct 13 '21 at 08:55

score 1 · Accepted Answer · answered Jan 21 '22 at 11:05

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

Mahamoutou · Answer 2 · 2021-10-14T12:55:14.027

Following solution will only work for Oracle database. Hope it will inspire you for HANA.

Your 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 dual union all
select 'a', to_date('07.01.2021', 'DD.MM.YYYY'), to_date('12.02.2021', 'DD.MM.YYYY'), 3 from dual union all
select 'a', to_date('11.01.2021', 'DD.MM.YYYY'), to_date('12.02.2021', 'DD.MM.YYYY'), 4 from dual union all
select 'b', to_date('05.01.2021', 'DD.MM.YYYY'), to_date('10.05.2021', 'DD.MM.YYYY'), 2 from dual union all
select 'b', to_date('11.01.2021', 'DD.MM.YYYY'), to_date('12.05.2021', 'DD.MM.YYYY'), 3 from dual union all
select 'b', to_date('14.02.2021', 'DD.MM.YYYY'), to_date('15.02.2021', 'DD.MM.YYYY'), 4 from dual
) 
;

First, Within row_numbered_tab view, I numbered all the rows per ITEM (row_number function), and I count all the previous rows per every row
Second, for each row, I generate as many rows as it has prev_count_rows
Then, I am able to implement your logic through views t and tt

With row_numbered_tab (ITEM, CREATION_DATE, ISSUE_DATE, QTY, prev_count_rows, rnb) as (
  select ITEM, CREATION_DATE, ISSUE_DATE, QTY
    , count(ISSUE_DATE)over(partition by ITEM order by CREATION_DATE, ISSUE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) prev_count_rows
    , row_number()over(partition by ITEM order by CREATION_DATE, ISSUE_DATE) rnb
  from Sample_Data t
)
, cte(ITEM, CREATION_DATE, ISSUE_DATE, QTY, prev_count_rows, rnb, RUNNING_REF_CREATION_DATE) as (
  select ITEM, CREATION_DATE, ISSUE_DATE, QTY, prev_count_rows, rnb, CREATION_DATE RUNNING_REF_CREATION_DATE
  from row_numbered_tab t
  union all
  select t.ITEM, t.CREATION_DATE, t.ISSUE_DATE, t.QTY, c.prev_count_rows, c.rnb - 1 rnb
    , case when t.ITEM = c.ITEM and t.rnb = c.rnb - 1 then c.RUNNING_REF_CREATION_DATE else t.CREATION_DATE end
  from row_numbered_tab t
  join cte c
  on t.rnb = c.rnb - 1
    and t.ITEM = c.ITEM
)
select ITEM, CREATION_DATE, ISSUE_DATE, QTY, O_QTY as Open_Quantity
from (
  select t.*, max(O_QTY)over(partition by ITEM, prev_count_rows)mx
  from (
  select ITEM, CREATION_DATE, ISSUE_DATE, QTY, prev_count_rows, RNB, RUNNING_REF_CREATION_DATE
  , NVL(
      sum(case when RUNNING_REF_CREATION_DATE < ISSUE_DATE then QTY else null end)
        over(
          partition by ITEM, prev_count_rows order by CREATION_DATE, ISSUE_DATE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
          )
      , 0 
      )O_QTY
  from cte
  ) t
)tt
where O_QTY = MX
order by ITEM, CREATION_DATE
;

demo

thanks, appreciate your efforts on this, I have tried the code on HANA but got some errors, converted some functions NLV() > IFNULL() and DECODE() > MAP() , and wrote CTE in 2 parts as it didn't accept referencing CTE in same definition, but still I got as result a bit different it shows for both item a and b 2 and 3 as open quantity on 2nd and 3th item, whereas for item b on last item I expect 2+3, seems to be not calculated — erkan, Oct 14 '21 at 12:24
My answer will only work for Oracle database. Unfortunately, I don't know much about HANA. — Mahamoutou, Oct 14 '21 at 12:52

how to aggregate data based on condition

2 Answers2