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.

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

How can I do this query?

CodePudding user response：

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

1. Select the values you want, i.e., only keep records for what you want
2. Calculate Min where status =0 and min where status =1 (columns - status_0_ts, status_1_ts)
3. 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: