Setting batch number for set of records in sql

I have following table in SQL

id,date,records
1,2019-03-28 01:22:12,5
2,2019-03-29 01:23:23,5
3,2019-03-30 01:28:54,5
4,2019-03-28 01:12:21,2
5,2019-03-12 01:08:11,1
6,2019-03-28 01:01:21,12
7,2019-03-12 01:02:11,1

What i am trying to achieve is set a batch number that should keep on increasing after moving sum value crosses 15 and the moving sum should reset as well, so i am trying to create batch for records that has total moving sum value as 15

For ex. if Moving sum becomes 15 the batch number value should increment, which would given me rows containing total value of 15.

so the output i am looking for is

id,date,records, moving_sum,batch_number
1,2019-03-28 01:22:12,5,5,1
2,2019-03-29 01:23:23,5,10,1
3,2019-03-30 01:28:54,5,15,1
4,2019-03-28 01:12:21,2,2,2
5,2019-03-12 01:08:11,1,1,2
6,2019-03-28 01:01:21,2,12,2
7,2019-03-12 01:02:11,1,1,3

Answer

You need a recursive query for this:

with 
    tab as (select t.*, row_number() over(order by id) rn from mytable t),
    cte as (
        select 
            id, 
            date, 
            records, 
            records moving_sum, 
            1 batch_number,
            rn
        from tab
        where rn = 1
        union all
        select
            t.id,
            t.date,
            t.records,
            case when c.moving_sum + t.records > 15 then t.records else c.moving_sum + t.records end,
            case when c.moving_sum + t.records > 15 then c.batch_number + 1 else c.batch_number end,
            t.rn
        from cte c
        inner join tab t on t.rn = c.rn + 1
    )
select id, date, records, moving_sum, batch_number from cte order by id

The syntax for recursive common table expressions slightly varies across databases, so you might need to adapt that a little depending on your database.

Also note that if ids start at 1, and are always incrementing without gaps, you don’t actually common table expression tab, and you can replace rn with id in the second common table expression.

Demo on DB Fiddle:

id | date       | records | moving_sum | batch_number
-: | :--------- | ------: | ---------: | -----------:
 1 | 2019-03-28 |       5 |          5 |            1
 2 | 2019-03-29 |       5 |         10 |            1
 3 | 2019-03-30 |       5 |         15 |            1
 4 | 2019-03-28 |       2 |          2 |            2
 5 | 2019-03-12 |       1 |          3 |            2
 6 | 2019-03-28 |      12 |         15 |            2
 7 | 2019-03-12 |       1 |          1 |            3