I have a table with two fields: time and value, both of them are integers and I want to find the continuous period with values bigger than 0 and then label the "value>0" period with continuous integers.
For example, if I have the input table like this:
I'd like the output table like this:
Using three CTE and two row_number() functions, I was able to do this. But I find the query too cumbersome. Does anyone have a more elegant code to do this?
I'm using sql server 2016 developer edition.
Here is my code:
CREATE TABLE #test1( [time] [int] NULL, [value] [int] NULL ) insert into #test1 values(1,0),(2,0),(3,1),(4,0) ,(5,1),(6,2),(7,0),(8,0) ,(9,1),(10,2),(11,3),(12,0) ,(13,0),(14,0),(15,1),(16,0); ;with a1 as (select *, [time] - row_number() over (order by [time]) as group_num from #test1 where value>0), a2 as (select distinct group_num from a1 ), a3 as (select group_num, row_number() over (order by group_num) as group_id from a2) select a1.*, a3.group_id from a1 left join a3 on a1.group_num = a3.group_num