Cumulative number by group SQL in one select statement

Question

This is what the data looks like:

ID	Date	Person	Status
1	01012020	API	NEW
1	02012020	REMCO	OKAY
1	03012020	API	RECALC
1	04012020	RUUD	OKAY
1	05012020	API	RECALC
1	06012020	MICHAEL	OKAY EXTRA INFO
1	07012020	ROY	OKAY
1	08012020	ROY	OKAY

I have to add a row that shows the version of this ID. The version is adding up when after a person other than API is handling the ID.

The sequence should start with 1.

So the result should look like:

ID	Date	Person	Status	VERSION
1	01012020	API	NEW	1
1	02012020	REMCO	OKAY	1
1	03012020	API	RECALC	2
1	04012020	RUUD	OKAY	2
1	05012020	API	RECALC	3
1	06012020	MICHAEL	OKAY EXTRA INFO	3
1	07012020	ROY	OKAY	4
1	08012020	ROY	OKAY	5

Sure you can use CTE or INNER JOIN. But I need this result to be in the SELECT statement. I have tried this:

SUM(    case    when STATUS IN ( 'NEW', 'RECALC') THEN 0
                else 1 end)
over( partition by ID order by Date asc)

as VERSION

This almost does it, but stops adding version numbers when a person is handeling the version. Which it should not do.

please ask if this isnt clear!

SUM(    case    when STATUS IN ( 'NEW', 'RECALC') THEN 0
                else 1 end)
over( partition by ID order by Date asc)

as VERSION

This stopped adding versions when a person keeps handeling the version. It should keep adding up versions.

It basically needs to add a version number when a person is done with it. So the row after a person reviewd the ID should add 1. API is not a person, everyone else is :)

Answer 1

The version is adding up when after a person other than API is handling the ID.

This reads like count of "non-API" rows over all preceding rows (not including the current rows.

You did not tag your database. A reasonably portable approach is to express the count with a conditional sum, and to use the rows between syntax to adjust the window frame:

select  t.*,
    1 + coalesce(
        sum(case when person != 'API' then 1 else 0 end) over(
            partition by id
            order by date
            rows between unbounded preceding and 1 preceding
        ),
        0
      ) as grp
from mytable t
order by id, date

fiddle:

id	date	person	status	grp
1	2020-01-01	API	NEW	1
1	2020-01-02	REMCO	OKAY	1
1	2020-01-03	API	RECALC	2
1	2020-01-04	RUUD	OKAY	2
1	2020-01-05	API	RECALC	3
1	2020-01-06	MICHAEL	OKAY EXTRA INFO	3
1	2020-01-07	ROY	OKAY	4
1	2020-01-08	ROY	OKAY	5