SQL increment counter that resets on zero – Optimization?

I have a table that contains consecutive dates and integers.

My goal is to add a counter that increments as each integer is zero and resets back to 1 if the integer is non-zero. I’m hoping that this can be optimized a bit as it can be an expensive query when run on the entire table.

Below is the code that is working. Thanks to everyone that takes a look.

DECLARE @test TABLE
( 
                    d DATE, 
                    n INT
)

INSERT INTO @test
VALUES
       ('2021-01-01', 0),
       ('2021-01-02', 0),
       ('2021-01-03', 0),
       ('2021-01-04', 5),
       ('2021-01-05', 0),
       ('2021-01-06', 0),
       ('2021-01-07', 10),
       ('2021-01-08', 10),
       ('2021-01-09', 0),
       ('2021-01-10', 0),
       ('2021-01-11', 9),
       ('2021-01-12', 0),
       ('2021-01-13', 0)

SELECT *
FROM   @test;

WITH test1
     AS (SELECT t1.d, 
                t1.n,
                CASE
                    WHEN t1.n = 0 THEN ( SELECT MAX(d)
                                         FROM  @test t2
                                         WHERE t2.d < t1.d
                                               AND t2.n <> 0 )
                    ELSE t1.d
                END AS 'dateFlag'
         FROM @test AS t1)
     SELECT d, 
            n, 
            ROW_NUMBER() OVER(PARTITION BY dateFlag
            ORDER BY d ASC)
     FROM   test1
     ORDER BY d

Answer

This is a type of gaps-and-islands problem.

There are many solutions, here is one

• Use a running conditional count to get an ID for each island
• Genrate a row number partitioned by that ID

WITH Groupings AS (
    SELECT *,
        GroupId = COUNT(CASE WHEN n <> 0 THEN 1 END) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)
    FROM @test
)
SELECT
  d,
  n,
  RN = ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY d)
FROM Groupings;

db<>fiddle