Splitting time from same column and storing into 2 column and finding difference between those 2 columns in sql

Question

Splitting time from same column and storing into 2 column and finding difference between those 2 columns in sql server.

select SourceName,Active,EventTimeStamp,
case when Active=1
then EventTimeStamp END as ActiveTime,

case when Active=0
then EventTimeStamp END as InactiveTime

from dbo.AllEvent
where SourceName='R2_20'
order by SourceName 
;


sname   active  EventTimeStamp      Active      Inavtive
R2_20       1   14:51.9     14:51.9     NULL
R2_20       0   14:53.9     NULL        14:53.9
R2_20       1   15:05.9     15:05.9     NULL
R2_20       0   15:07.9     NULL        15:07.9
R2_20       1   15:15.9     15:15.9     NULL
R2_20       0   15:17.9     NULL        15:17.9
R2_20       1   15:26.0     15:26.0     NULL
R2_20       0   15:28.0     NULL        15:28.0
R2_20       1   15:36.0     15:36.0     NULL
R2_20       0   15:38.0     NULL        15:38.0

Answer 1

If the version of SQL Server you are using supports the LEAD function, use it. But this is only my guess based on the data you provided.

SELECT
  SourceName sname,
  Active active,
  EventTimeStamp start,
  [end],
  DATEDIFF(millisecond, EventTimeStamp, [end]) difference
FROM (
  SELECT
    *,
    LEAD(EventTimeStamp)
      OVER (PARTITION BY SourceName
            ORDER BY EventTimeStamp, Active DESC) [end]
  FROM AllEvents
) t
WHERE Active = 1;

But for a more complex data set, the following query may be preferable.

WITH
  AllEvents AS (
    SELECT
      sn SourceName,
      CAST(a as INT) Active,
      CAST(ts as TIME) EventTimeStamp
    FROM (VALUES
      ('R2_20', 1, '00:14:51.9'),
      ('R2_20', 1, '00:14:52.9'),
      ('R2_20', 0, '00:14:53.9'),
      ('R2_20', 1, '00:15:05.9'),
      ('R2_20', 0, '00:15:07.9'),
      ('R2_20', 1, '00:15:15.9'),
      ('R2_20', 1, '00:15:16.9'),
      ('R2_20', 0, '00:15:17.5'),
      ('R2_20', 0, '00:15:17.9'),
      ('R2_20', 1, '00:15:26.0'),
      ('R2_20', 0, '00:15:28.0'),
      ('R2_20', 1, '00:15:36.0'),
      ('R2_20', 0, '00:15:37.0'),
      ('R2_20', 0, '00:15:38.0')
    ) t(sn, a, ts)
  ),
  a AS (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY SourceName ORDER BY EventTimeStamp) -
        ROW_NUMBER() OVER (PARTITION BY SourceName, Active ORDER BY EventTimeStamp) g
    FROM AllEvents
  ),
  b AS (
    SELECT
      SourceName,
      Active,
      IIF(Active = 0, MAX(EventTimeStamp), MIN(EventTimeStamp)) start
    FROM a
    GROUP BY SourceName, Active, g
  ),
  c AS (
    SELECT
      *,
      LEAD(start) OVER (PARTITION BY SourceName ORDER BY start, Active DESC) [end],
      DATEDIFF(millisecond,
               start,
               LEAD(start) OVER (PARTITION BY SourceName
                                 ORDER BY start, Active DESC)) difference
    FROM b
  )
SELECT * FROM c WHERE Active = 1 ORDER BY start;