# Elegant Code to find continuous time period and generate id

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
``````
• 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? Commented Jan 2, 2017 at 21:36

## 1 Answer

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

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