sql - How to get conditional sequence

Question

I have a question about sequence function in SQL Server.

First, I created a base table. Here is my code.

CREATE TABLE TEST2(
    SEQ int IDENTITY (1, 1) NOT NULL,
    Dates date,
    CNT int,
)
INSERT INTO TEST2 (Dates, CNT)
VALUES 
('2020-01-01', 0),
('2020-01-02', 0),
('2020-01-03', 0),
('2020-01-04', 1),
('2020-01-05', 0),
('2020-01-06', 1),
('2020-01-07', 0),
('2020-01-08', 0),
('2020-01-09', 0),
('2020-01-10', 0),
('2020-01-11', 0),
('2020-01-09', 2),
('2020-01-10', 0),
('2020-01-11', 0)

Here my attempt code.

CASE WHEN CNT != 0
THEN 0
ELSE CNT = 0
THEN (ROW_NUMBER() OVER(ORDER BY Dates))
END NEW_SEQ

It consists of two columns(Dates, CNT). And I want to get the following result using the WHEN CASE expressions.

Here is my example results.

SEQ	Dates	CNT	NEW_SEQ
1	2020-01-01	0	0
2	2020-01-02	0	2
3	2020-01-03	0	3
4	2020-01-04	1	0
5	2020-01-05	0	1
6	2020-01-06	1	0
7	2020-01-07	0	1
8	2020-01-08	0	2
9	2020-01-09	0	3
10	2020-01-10	0	4
11	2020-01-11	0	5
12	2020-01-09	2	0
13	2020-01-10	0	1
14	2020-01-11	0	2

question from:https://stackoverflow.com/questions/65839634/how-to-get-conditional-sequence

Answer 1

You can use analytical function as follows:

select t.Dates, t.CNT, 
       row_number() over (partition by sm order by seq) - 1 as NEW_SEQ
from
(select t.*,
       sum(case when cnt = 0 then 0 else 1 end) over (order by seq) as sm
  from test t) t