SQL Increment based on another column-CodePudding

I have a table that I need to assign a number sequence to, then have the number sequence restarts to 1 again based on a value in another column.

Here is my table:

Date	Name	Indicator
01/12/2022	Test1	1
02/12/2022	Test2	NULL
03/12/2022	Test3	NULL
04/12/2022	Test4	NULL
05/12/2022	Test5	1
06/12/2022	Test6	NULL
07/12/2022	Test7	1
08/12/2022	Test8	NULL

What I need is to add an "Instance" column to the end, this column simply continues the number sequence until it hits a row with a 1 in the "Indicator" column again then it resets.

So my desired outcome would be:

Date	Name	Indicator	Instance
01/12/2022	Test1	1	1
02/12/2022	Test2	NULL	2
03/12/2022	Test3	NULL	3
04/12/2022	Test4	NULL	4
05/12/2022	Test5	1	1
06/12/2022	Test6	NULL	2
07/12/2022	Test7	1	1
08/12/2022	Test8	NULL	2

I've tried playing aound with DENSE_RANK() over (Order by Date)

But I understandly end up with this:

Date	Name	Indicator	Instance
01/12/2022	Test1	1	1
02/12/2022	Test2	NULL	2
03/12/2022	Test3	NULL	3
04/12/2022	Test4	NULL	4
05/12/2022	Test5	1	1
06/12/2022	Test6	NULL	6
07/12/2022	Test7	1	1
08/12/2022	Test8	NULL	8

The sequence carries on, it does not reset at the 1 (in the indicator column).

CodePudding user response：

You can do:

select x.*, row_number() over(partition by g order by date) as instance
from (select t.*, sum(indicator) over(order by date) as g from t) x

Result:

 Date        Name   Indicator  g  instance 
 ----------- ------ ---------- -- -------- 
 2022-01-12  Test1  1          1  1        
 2022-02-12  Test2  null       1  2        
 2022-03-12  Test3  null       1  3        
 2022-04-12  Test4  null       1  4        
 2022-05-12  Test5  1          2  1        
 2022-06-12  Test6  null       2  2        
 2022-07-12  Test7  1          3  1        
 2022-08-12  Test8  null       3  2

See running example at db<>fiddle.