Q: How can I get the first state after a certain condition in SQL? In SQL, I have data that I have brought with a certain condition. Here's how I pull data with [Status] = 0 from the table. But the table still has data with [Status] = 1 and [Status] = 2. After the data I want [Status] = 0, I also want to get [CL_Name] with the same name with [Status] = 1 or [Status] = 2. I couldn't do this. My sample table is as follows.




Name
TIMESTAMP
Equipment
CL_Name
Status




gluing_conveyor
2023-01-03 16:45:34.243
A
Stabilizer
0


gluing_conveyor
2023-01-03 16:50:34.247
A
Stabilizer
0


gluing_conveyor
2023-01-03 16:55:34.247
A
Stabilizer
0


gluing_conveyor
2023-01-03 17:00:34.247
A
Stabilizer
1


gluing_conveyor
2023-01-03 17:05:34.247
A
Stabilizer
1


rolling platform
2023-01-03 18:05:34.247
B
Vacuum
0


rolling platform
2023-01-03 18:10:34.247
B
Vacuum
1


rolling platform
2023-01-03 18:15:34.247
B
Vacuum
1




What I want is to return [Status] = 0 here and each [CL_Name] [Status] = 0 depending on whether the next [Status] = 1 or [Status] = 2
My SQL code is like this:
 SELECT [Name]
      ,[TIMESTAMP]
      ,[Equipment]
      ,[CL_Name]
      ,[Status]
      --,(case when [Status] > 0 then [TIMESTAMP] end) OVER ( PARTITION BY [CL_Name]) as EndTime
  FROM [dbo].[Cockpit]
  where [TIMESTAMP] > DATEADD(day,-1,GETDATE()) and [Status] = 0
  group by [Name], [Equipment], [Name], [TIMESTAMP], [Status]

The table I'm trying to make looks like this




Name
TIMESTAMP
Equipment
CL_Name
Status
TotalTimeMin




gluing_conveyor
2023-01-03 16:45:34.243
A
Stabilizer
0
15


gluing_conveyor
2023-01-03 17:00:34.247
A
Stabilizer
1



rolling platform
2023-01-03 18:05:34.247
B
Vacuum
0
5


rolling platform
2023-01-03 18:10:34.247
B
Vacuum
1





How can I do this query?

A: Syntax is slightly different because don't have SQL server now, but logic should be same.

*

*Select the values you want, i.e., only keep records for what you want

*Calculate Min where status =0 and min where status =1 (columns - status_0_ts, status_1_ts)

*if the Status =0 then subtract those 2 columns and calculate the difference else NULL

P.S. - This is missing the [TIMESTAMP] > DATEADD(day,-1,GETDATE()) feel free to add it where you see fit
Code
Select *, 
min(case when "Status" = 0 then "min_timestamp" else null end) over (partition by "Name", "CL_Name", "Equipment") status_0_ts,
min(case when "Status" = 1 then "min_timestamp" else null end) over (partition by "Name", "CL_Name", "Equipment") status_1_ts,
CASE WHEN "Status" = 0 
  then 
    min(case when "Status" = 0 then "min_timestamp" else null end) over (partition by "Name", "CL_Name", "Equipment") -
    min(case when "Status" = 1 then "min_timestamp" else null end) over (partition by "Name", "CL_Name", "Equipment") 
  else null 
end as time_diff
from
(
    SELECT "Name", "CL_Name", "Equipment", "Status", min("TIMESTAMP") as "min_timestamp"
    FROM TABLE1
    group by "Name", "CL_Name", "Equipment", "Status"
) K
order by "Name" asc ,"Status" asc

Output:





Name
CL_Name
Equipment
Status
min_timestamp
status_0_ts
status_1_ts
time_diff




gluing_conveyor
Stabilizer
A
0
2023-01-03T16:45:34Z
2023-01-03T16:45:34Z
2023-01-03T17:00:34Z
0 years 0 mons 0 days 0 hours -15 mins -0.00 secs


gluing_conveyor
Stabilizer
A
1
2023-01-03T17:00:34Z
2023-01-03T16:45:34Z
2023-01-03T17:00:34Z
(null)


rolling platform
Vacuum
B
0
2023-01-03T18:05:34Z
2023-01-03T18:05:34Z
2023-01-03T18:10:34Z
0 years 0 mons 0 days 0 hours -5 mins -0.00 secs


rolling platform
Vacuum
B
1
2023-01-03T18:10:34Z
2023-01-03T18:05:34Z
2023-01-03T18:10:34Z
(null)



