drop table #temp
create table #temp(id varchar(20), win bit, dt datetime default getdate())
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
--insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1)
;with temp
as
(
select * , row_number() over(partition by id order by dt) rownum
from #temp
), rs1
as
(
select --*
a.id
, a.rownum
, max(b.rownum) max_rownum
, max(b.dt) max_dt
, isnull(count(case when b.win = 1 and a.rownum-b.rownum <> 1 then 1 end),0) cnt
, isnull(min(case when a.rownum-b.rownum = 1 then convert(int,b.win) end),0) prev1_row
from temp a inner join temp b
on a.id = b.id
and a.rownum in (b.rownum+1, b.rownum, b.rownum-1, b.rownum-2)
group by
a.id
, a.rownum
having isnull(count(case when b.win = 1 and a.rownum-b.rownum <> 1 then 1 end),0) = 3
), rs2
as
(
select *, row_number() over(partition by id order by rownum) rownum2
from rs1
)
select *
from rs2
where prev1_row = 0 or rownum2 % 3 = 1
order by id, rownum