[SOLVED] Summing up column values of rows based on consecutive date in AMDP/MYSQL

Summing up column values of rows based on consecutive date in AMDP/MYSQL

I am trying to convert one SAP ABAP code into newly introduced language SAP AMDP(ABAP Managed Database Procedures) which is based on MYSQL.

I want to sum up column values of of rows if they with consecutive dates meaning if Start date of Next row is Next day of End date of current row then the value should sum up.

For ex: Below is my source table

EMP Startdate   Enddate amount 
1   1/1/2020    1/3/2020    2
1   1/4/2020    1/7/2020    3
1   1/8/2020    1/10/2020   4

1   1/15/2020   1/18/2020   5

2   1/3/2020    1/6/2020    3

2   1/12/2020   1/15/2020   4
2   1/16/2020   1/20/2020   5

3   1/4/2020    1/8/2020    5
3   1/9/2020    1/11/2020   6

3   1/14/2020   1/18/2020   7

3   1/21/2020   1/24/2020   7
3   1/25/2020   1/27/2020   5

The Second row's start date(4-Jan) is next day of End date(3-jan) of first row and same for 3rd row.

So result should come as single row with start date of first row and end date of third row and total of all three rows. Expected result should be like below.

EMP Startdate   Enddate amount

1   1/1/2020    1/10/2020   9

1   1/15/2020   1/18/2020   5

2   1/3/2020    1/6/2020    3

2   1/12/2020   1/20/2020   9

3   1/4/2020    1/11/2020   11

3   1/14/2020   1/18/2020   7

3   1/21/2020   1/27/2020   12

Solution

This is a gaps and islands problem, where you want to group together "adjacent" rows. I have no knowledge in AMDP, but in MySQL you could approach this with window functions:

select emp, min(start_date) start_date, max(end_date) end_date, sum(amount) amount      
from (
    select t.*, sum(case when start_date = lag_end_date + interval 1 day then 0 else 1 end) over(partition by emp order by start_date) grp
    from (
        select t.*, lag(end_date) over(partition by emp order by start_date) lag_end_date
        from mytable t
    ) t
) t
group by emp, grp