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:

    [time] [int] NULL,
    [value] [int] NULL

insert into #test1

;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
  • I don't get the meaning of group_num and group_id in your example. If these are your labels, I don't fully understand how they are continuous as you previously mentioned. Could you elaborate a little more? – joanolo Jan 2 '17 at 21:36

You just need to apply DENSE_RANK to the result of the first CTE - Demo

     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value > 0)
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
|improve this answer|||||
  • Thanks for your prompt answer! I knew this "Dense_rank" function but never used it... – Jason Jan 2 '17 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.