0

I have this table EFF on SAP HANA:

OBJECT;PN;MANUFACTURER;MONTH;QTY   
OBJ1;PN1;MAN1;201601;1   
OBJ1;PN1;MAN1;201602;1   
OBJ1;PN1;MAN1;201603;2   
OBJ1;PN1;MAN1;201604;1   
OBJ1;PN1;MAN1;201605;1   
OBJ1;PN1;MAN1;201606;1

I would like to obtain with a SQL statement an aggregated view of the table by OBJECT / PN / MANUF / QTY but sorted by date. Here is what I want:

OBJECT;PN;MANUFACTURER;DATE_FROM;DATE_TO;QTY    
OBJ1;PN1;MAN1;201601;201602;1 (from 01-2016 to 02-2016, OBJ1 contained 1 PN1)   
OBJ1;PN1;MAN1;201603;201603;2 (from 03-2016 to 03-2016, OBJ1 contained 2 PN1)   
OBJ1;PN1;MAN1;201604;201606;1 (from 04-2016 to 06-2016, OBJ1 contained 1 PN1)

I've already tested many solutions but nothing works well... I always obtain :

  • 1 line from 01-2016 to 06-2016 with qty = 1
  • 1 line from 03-2016 to 03-2016 with qty = 2

It is not enough ...

I've been looking to Window functions in SAP HANA like FIRST_VALUE (partition by...) and LAST_VALUE but it's not working properly...

Do you have any idea, please ?

Note : I already have implemented a Stored Procedure with a Cursor to do it, and it works (in several hours), but I need something much faster. Because it concerns billions of lines.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
KrasshX
  • 142
  • 4
  • 20
  • The desired logic is not clear yet. Do you want a new grouping whenever the QTY changes? In that case, you should look for "finding longest streak" examples with window functions. – Lars Br. Jul 21 '16 at 08:54
  • Thanks for you answer Lars. Yes I do want a solution grouping lines by quantities but respecting the chronological order. I'm writing a solution based on `LEAD` / `LAG` and `ROW_NUMBER` functions. – KrasshX Jul 21 '16 at 09:02

1 Answers1

0

I think what you want to get is as following

select 
    "OBJECT", 
    PN, 
    MANUFACTURER, 
    Month as fromMonth, 
    Lead(Month,1) over (partition by  "OBJECT", PN, MANUFACTURER Order by Month) as toMonth,
    sum(Qty) as qty 
from EFF
group by
    "OBJECT", 
    PN, 
    MANUFACTURER,
    Month

Output with your sample data is as in below screenshot

enter image description here

As you mentioned I used the SQL Lead function for calculating next month I used Group By clause for SUM() aggregate function to collect Quantities per month on the bases of Object, PN and Manufacturer

I hope it helps,

Eralper
  • 6,461
  • 2
  • 21
  • 27