SQL query to find 3 consective records and rspective data

Question

Need to pick the data where values are >=0.7 and appearing at least 3 consecutive times then

            hourly recorded per day
Hours         00    0100  0200   0300   0400   0500 
ValueNumber   .7    .8    .5     .4     .8     .85     so on

Issue is I can pick the data which is >=0.7 and appearing 3 times but its not consecutive. Any help much appreciated.

select distinct  encounterId, COUNT(valueNumber) 
FROM table pta 
where interventionId in (2201,2202)
  and pta.valueNumber >=0.7
  and pta.valueNumber is not null
group by encounterId 
having COUNT(hours)>=3

If you need more information please do ask. Database is huge and every single column is recorded as "InterventionID". Above is criteria of search once this done I can put this in temp table and then pull record. Looking forward to some kind of solution.

Thanks

Answer 1

WITH cte AS ( SELECT series,
                     date_time,
                     value, 
                     LEAD(value) OVER (PARTITION BY series 
                                       ORDER BY date_time) lead1, 
                     LEAD(value, 2) OVER (PARTITION BY series 
                                          ORDER BY date_time) lead2
              FROM datatable 
              WHERE value >= 0.7 )
SELECT series, date_time, value
FROM cte
WHERE lead1 >= 0.7
  AND lead2 >= 0.7

If no series, remove it from fieldsets and PARTITION BY clause.