Start at condition and stop when criteria is met

Question

i'm new to MS SQL and i have to carry out the following task. What i want to output is the time of the first occurence of Stateid = 4, go through the list and return the time when i meet a either stateid = 3 or stateid = 7. I can't find the methods to use to achieve this. Can someone help please ?

My table

.

Desired Result

Answer 1

WITH cte AS (
SELECT *,
       MIN(CASE WHEN stateid IN (3,7)
                THEN datetime
                END) OVER ( PARTITION BY name
                            ORDER BY datetime ASC
                            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as datetimestop
FROM sourcetable
) 
SELECT name,
       datetime,
       datetimestop,
       description
FROM cte
WHERE stateid = 4
/* AND datetimestop IS NOT NULL */

The query assumes that (name, datetime) is unique. If not then the result is not deterministic (in the case when there exists a record pair with the same name and datetime where stateid=4 in one record and stateid in (3,7) in another record).

Answer 2

Let me preface this answer by stating that I don't know how well this solution would scale, but it does work.

You can retrieve the results you're after by selecting the StateId = 4 rows only and using CROSS APPLY and a subquery to select the StateId = 3 or 7 rows that are after the StateId = 4 rows.

Code:

SELECT s1.Name,
  MIN(s1.LogTime) AS DateTime, -- Get the first StateId = 4 date time value
  s2.LogTime AS DateTimeStop,
  s2.Description
FROM Data s1
CROSS APPLY (
  SELECT TOP (1) LogTime, Name, StateId, Description
  FROM Data 
  WHERE StateId IN (3, 7) -- StateId 3 or 7 rows only
    AND Name = s1.name -- Name matching for multi name data sets
    AND LogTime > s1.LogTime -- The StateId 3 or 7 row should be after the StateId 4 row
  ORDER BY LogTime ASC -- We're using TOP (1) to select the first value only, so need to order by date time ascending to get the correct value
) s2
WHERE s1.StateId = 4 -- Select only the StateId = 4 rows in the outer select
GROUP BY s1.Name, s2.LogTime, s2.Description
ORDER BY s2.LogTime ASC

Results:

Name    DateTime                DateTimeStop            Description
-------------------------------------------------------------------
Jack    25/10/2019 07:47:45     25/10/2019 07:48:37     StopWork
Jack    25/10/2019 08:01:37     25/10/2019 08:05:50     
Jack    25/10/2019 09:33:23     25/10/2019 09:34:47     StopWork
Jack    25/10/2019 09:51:20     25/10/2019 11:05:02     StopWork

You can see it in action in this db<>fiddle. I've also included extra data with a different name to show how the query will handle different names in the data set.