selecting consecutive records from a table based on a column value

mnmhema,

Check if the following helps yu. Missed out on a condition in the derived table part of the query following the cte .. :)

create table #temp2
(
           cola varchar(100),
           colb varchar(100),
           colc varchar(100)
)

insert #temp2 select 100,'1100100','free'
insert #temp2 select 101,'1100101','free'
insert #temp2 select 102,'1100110','free'
insert #temp2 select 103,'1100111','assigned'
insert #temp2 select 104,'1101000','free'
insert #temp2 select 105,'1101001','free'
insert #temp2 select 106,'1101011','free'
insert #temp2 select 107,'1101100','free'
insert #temp2 select 108,'1101101','free'
insert #temp2 select 109,'1101101','free'
insert #temp2 select 110,'1101110','free'
insert #temp2 select 111,'1101111','free'
insert #temp2 select 112,'1110000','free'

--select * from #temp2

;with cte
as
(
           select *,1 as level
           from #temp2
           where colb like '%00' and colc='free'
           UNION ALL
           select t.*,c.level+1
           from #temp2 t inner join cte c on t.cola=c.cola+1
)
select t.* from #temp2 t,
(
select top 1 cola as cola from cte
where level=4 and colc='free'--to get the 1st rec thts free
order by cola
) tt
where t.cola between tt.cola-3 and tt.cola

drop table #temp2

Thanks,

JK

Jayakumaur

<Please mark as 'answered' if this answers your query>

<Please mark as 'helpful' if this was helpful to your query>