The result should look like this:
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 | 3 |
1 | 07012020 | ROY | OKAY | 4 |
1 | 08012020 | ROY | OKAY | 5 |
I need to generate this result with a SELECT statement.
I have tried this code:
SUM( case when STATUS IN ( 'NEW', 'RECALC') THEN 0
else 1 end)
over( partition by ID order by Date asc)
as VERSION
But this code stops adding versions when a person is handeling the version. It should keep adding up versions.
I need to keep all the fenced code blocks and have the new content in the Markdown format.
I need to generate the following result with a SELECT statement:
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 | 3 |
1 | 07012020 | ROY | OKAY | 4 |
1 | 08012020 | ROY | OKAY | 5 |
I need to generate this result by adding a version number to the SELECT statement, which will add up when a person (not including API) has handled the ID. The sequence should start with 1.
I have tried this code:
SUM( case when STATUS IN ( 'NEW', 'RECALC') THEN 0
else 1 end)
over( partition by ID order by Date asc)
as VERSION
But this code stops adding versions when a person is handeling the version. It should keep adding up versions.