There is a data set with continuous data coming from the table. I have a column with [Status] in it. I'm trying to calculate the time in between by taking the date from the row with [Status] = 0 and taking the time as "EndTime" from the first row with [Status] > 0 as "StartTime". But I couldn't do the query. My table is as below:

The table I'm trying to make is

Here, the algorithm is to find the date data as "[Status]" = 0, take the date data as "StartTime" and then find the first "[Status]" = 1, take the date data and take that data as "EndTime" and try to find the time difference between the two.

I wrote my sql query as follows, but I was not completely successful.

  SELECT [LINE], [CL_Name], StartTime, EndTime, 
      DATEDIFF(second, startTime, endTime) as TotalTime
 FROM ( select [LINE], [CL_Name], [TIMESTAMP], [Status],
    Min(case when [Status] > 0 then [TIMESTAMP] end) as EndTime,
    Min(case when [Status] = 0 then [TIMESTAMP] end) as StartTime
  from [dbo].[CL]
  group by [LINE], [CL_Name], [_VALUE], [TIMESTAMP], [Status]) as tt
  where StartTime IS NOT NULL and EndTime IS NOT NULL
  Order by [TIMESTAMP] desc

The output I wanted

CodePudding user response：

Try the following, explanation within query comments:

/* check if the status is changed over the increasing time,
   and set a value of 1 whenever a change is occur
*/
WITH Check_Changes AS
(
  SELECT *,
    IIF(Status <> LAG(Status, 1, Status) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP), 1 , 0) flag
  FROM CL
),
/* create groups using a running sum of the calculated flag in
   the previous CTE. This will give the same number for the 
   consecutive similar status values i.e. for (0000,11,000,11) it 
   will be (0000,11,222,33)
*/
Craete_Groups AS
(
  SELECT *, 
    SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP)  
    CASE WHEN SUM(flag) OVER (PARTITION BY LINE, CL_NAME ORDER BY TIMESTAMP) % 2 = Status
    THEN 0 ELSE 1 END AS grp
FROM Check_Changes
/*  CASE WHEN SUM(flag) OVER ... is to check if the status begins with a 0 value. 
    In the typical case, the even groups will be within status=0 and the odd groups will be within status=1.
    This will add 1 to the group value if the status does not start with 0 i.e. for status = (1111,000,111)
    it will be (1111,222,333) instead of (0000,111,222)
*/
)
/* now you can connect every two consecutive groups together by using grp/2
i.e. groups (0000,11,222,33) will be (0000,00,111,11)
*/
SELECT LINE, CL_Name,
       MIN(CASE WHEN Status = 0 THEN TIMESTAMP END) StartTime, 
       MAX(CASE WHEN Status = 1 THEN TIMESTAMP END) EndTime, 
       DATEDIFF(MINUTE, 
                MIN(CASE WHEN Status = 0  THEN TIMESTAMP END), 
                MAX(CASE WHEN Status = 1  THEN TIMESTAMP END)
               ) TotalTime
FROM Craete_Groups
GROUP BY LINE, CL_Name, grp/2
ORDER BY LINE, CL_Name, MIN(TIMESTAMP)

See a demo on an extended data sample.

CodePudding user response：

On solution you can try is as follows, use the analytic function to partition and order your data based on the Timestamp.

You want the first row per Line and the first row per Line where the Status is 1.

Your desired results don't agree with your description however where you use the last row where Status = 1 - in which case change the Min below to Max.

with s as (
  select *, 
    Row_Number() over(partition by line, CL_Name, order by timestamp) rn,
    Min(case when status = 1 then timestamp end) over(partition by line, CL_Name) endtime
  from t
)
select 
  line, 
  CL_Name, 
  Timestamp 
  StartTime, 
  EndTime, 
  DateDiff(minute, Timestamp, EndTime) TotalTime
from s
where rn = 1;