Select rows starting from specified value until another specified value
A suggestion that should work in 2008 version.
Tested at rextester.com:
with
end_points as -- find start and end points
( select id, time, value
from table_x
where value in (15, 16)
),
start_points as -- only the start points
( select id, time, value
from end_points
where value = 15
)
select
t.id, t.time, t.value
from
start_points as s
outer apply -- find where each island ends
( select top (1) ep.*
from end_points as ep
where s.id = ep.id
and s.time < ep.time
order by ep.time
) as e
cross apply -- and run through each island
( select p.id, p.time, p.value,
rn = row_number() over (order by p.time)
from table_x as p
where s.id = p.id
and s.time <= p.time
and ( p.time < e.time
or p.time = e.time and e.value = 16
or e.time is null)
) as t
where
t.rn <= 100
order by
t.id, t.time ;
More info:
- Using Common Table Expressions
- Using APPLY
- ROW_NUMBER (Transact-SQL)