Find consecutive sequence

Question

TS_Date TS_Person TS_Value
11/1/2021 Stephen 8
11/2/2021 Stephen 0
11/3/2021 Stephen 4
11/4/2021 Stephen 4
11/1/2021 Susan 8
11/2/2021 Susan 8
11/3/2021 Susan 6
11/4/2021 Susan 0

I am looking for a consecutive sequence of 3 values greater than 0. In the above data Stephen does not satisfy the criteria because he had a break on 11/2/2021. Susan satisfies the criteria because she worked 3 consecutive days. I have tried building a stored procedure that goes through each row grouping on name and ordering by date but I am struggling. I would like to write the name and TRUE/FALSE out to a temp table for the data

TS_Person TS_Value
Stephen 0
Susan 1

Answer 1

Create the following procedure

Create Procedure GetSequenceValue @SeqValue int As 
;With cteIsland As
(Select TS_Date, TS_Person, TS_Value,
  Row_Number() Over(Partition By TS_Person Order By TS_Date) - DateDiff(day, '20000101', TS_Date) As Island
From #Sample
Where TS_Value <> 0),

cteCounts As
(Select TS_Person, Island, Count(*) As Cnt
From cteIsland
Group By TS_Person, Island),

cteMaxCounts As
(Select TS_Person, Max(Cnt) As MaxCount
From cteCounts
Group By TS_Person)

Select TS_Person, Case When MaxCount >= @SeqValue Then 1 Else 0 End As TS_Value
From cteMaxCounts;
go

Then just pass the number of consecutive days you are interested in to the procedure. For example

Exec GetSequenceValue 3

will return 1 for all people with a consecutive sequence of 3 or more days.

Tom

Answer 2

Check a relatively compact attempt:

select t1.TS_Person, iif(count(t3.TS_Value) > 0, 1, 0) as TS_Value
from MyTable t1
left join MyTable t2 on t2.TS_Person = t1.TS_Person and datediff(day, t1.TS_Date, t2.TS_Date) = 1 and t2.TS_Value <> 0
left join MyTable t3 on t3.TS_Person = t2.TS_Person and datediff(day, t2.TS_Date, t3.TS_Date) = 1 and t3.TS_Value <> 0
where t1.TS_Value <> 0
group by t1.TS_Person