Search
Search

# how to calculate the cumulative sum with certain interval in SAP HANA

Hi Experts,

I have a scenario in which I have a column with Boolean values and need to calculate the cumulative values in the interval where there are consecutive ones as mentioned below,

ID          Date                           column 1

1         9-14-2014 14:22:00          1

1          9-14-2014 14:25:00          1

2          9-14-2014 13:22:00          0

1          9-14-2014 15:02:00          0

1          9-14-2014 14:37:00          0

2          9-14-2014 14:25:00          1

2          9-14-2014 14:32:00          1

1          9-14-2014 14:05:00          1

2          9-14-2014 14:45:00          0

2          9-14-2014 14:59:00          0

1          9-14-2014 15:12:00          1

1          9-14-2014 15:18:00          1

1          9-14-2014 15:21:00          1

First needs to group by 'ID' and Order By ' Date' and calculate the calculated column 'cumulative sum' for the consecutive ones in 'column1' as

ID          Date                           column 1          Cumulative sum

1          9-14-2014 14:05:00          1                    1

1          9-14-2014 14:22:00          1                    2

1          9-14-2014 14:25:00          1                    3

1          9-14-2014 14:37:00          0                    0

1          9-14-2014 15:02:00          0                    0

1          9-14-2014 15:12:00          1                    1

1          9-14-2014 15:18:00          1                    2

1          9-14-2014 15:21:00          1                    3

2          9-14-2014 13:22:00          0                    0

2          9-14-2014 14:25:00          1                    1

2          9-14-2014 14:32:00          1                    2

2          9-14-2014 14:45:00          0                    0

2          9-14-2014 14:59:00          0                    0

Is there any function and way to calculate this  without loops in procedure?