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
Advertisement
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;