sum of time based on flag from multiple rows SQL Server

Question

I am having tough time executing one of the query. I need to find the ignition time based on the flag from same table. table is like below,

UnitId      eventtime               ign
----------- ----------------------- -----
356         2011-05-04 10:41:00.000 1
356         2011-05-04 10:42:00.000 1
356         2011-05-04 10:43:00.000 1
356         2011-05-04 10:45:00.000 1
356         2011-05-04 10:47:00.000 1
356         2011-05-04 10:48:00.000 0
356         2011-05-04 11:14:00.000 1
356         2011-05-04 11:14:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:15:00.000 1
356         2011-05-04 11:16:00.000 0
356         2011-05-04 11:16:00.000 0
356         2011-05-04 11:16:00.000 0
356         2011-05-04 14:49:00.000 1
356         2011-05-04 14:50:00.000 1
356         2011-05-04 14:50:00.000 1
356         2011-05-04 14:51:00.000 1
356         2011-05-04 14:52:00.000 0
356         2011-05-04 14:52:00.000 0
356         2011-05-04 20:52:00.000 0

Here, The Ign flag will determine the ignition_on and iginition_off time. So above table we can have the ignition pair of

2011-05-04 10:41:00.000 - 2011-05-04 10:48:00.000
2011-05-04 11:14:00.000 - 2011-05-04 11:16:00.000
2011-05-04 14:49:00.000 - 2011-05-04 14:52:00.000

So from looking at the above pair I can say that my unit runs for 7 + 2 + 3 = 12 minutes. I DONT WANT THE ABOVE PAIRS IN MY RESULT, ITS FOR EXAMPLE ONLY. MY AIM HERE IS TO GET THIS RESULT OF 12 MINUTES.

How can I achieve it using single query, right now I am using the CURSOR loop but this thing takes much more time for multiple days and multiple units. Is there anyway by which I can achieve it without the CURSOR?

Answer 1

If there was an additional criterion for us to distinguish contiguous sequences of events with identical ign values from one another, we could take from each sequence with ign=1 its earliest event and link it with the earliest event of the corresponding ign=0 sequence.

It is possible to add such a criterion, as you will see below. I'm going to post the solution first, then explain how it works.

First, the setup:

DECLARE @atable TABLE (
  Id int IDENTITY,
  UnitId int,
  eventtime datetime,
  ign bit
);
INSERT INTO @atable (UnitId, eventtime, ign)
SELECT 356, '2011-05-04 10:41:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:42:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:43:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:45:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:47:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 10:48:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:14:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:14:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:15:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 11:16:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 14:49:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:50:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:50:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:51:00.000', 1 UNION ALL
SELECT 356, '2011-05-04 14:52:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 14:52:00.000', 0 UNION ALL
SELECT 356, '2011-05-04 20:52:00.000', 0;

And now the query:

WITH
marked AS (
  SELECT
    *,
    Grp = ROW_NUMBER() OVER (PARTITION BY UnitId ORDER BY eventtime) -
     ROW_NUMBER() OVER (PARTITION BY UnitId, ign ORDER BY eventtime)
  FROM @atable
),
ranked AS (
  SELECT
    *,
    seqRank = DENSE_RANK() OVER (PARTITION BY UnitId, ign ORDER BY Grp),
    eventRank = ROW_NUMBER() OVER (PARTITION BY UnitId, ign, Grp ORDER BY eventtime)
  FROM marked
),
final AS (
  SELECT
    s.UnitId,
    EventStart = s.eventtime,
    EventEnd   = e.eventtime
  FROM ranked s
    INNER JOIN ranked e ON s.UnitId = e.UnitId AND s.seqRank = e.seqRank
  WHERE s.ign = 1
    AND e.ign = 0
    AND s.eventRank = 1
    AND e.eventRank = 1
)
SELECT *
FROM final
ORDER BY
  UnitId,
  EventStart

This is how it works.

The marked common table expression (CTE) provides us with the additional criterion I was talking about at the beginning. The result set it produces looks like this:

Id  UnitId  eventtime                ign  Grp
--  ------  -----------------------  ---  ---
1   356     2011-05-04 10:41:00.000  1    0
2   356     2011-05-04 10:42:00.000  1    0
3   356     2011-05-04 10:43:00.000  1    0
4   356     2011-05-04 10:45:00.000  1    0
5   356     2011-05-04 10:47:00.000  1    0
6   356     2011-05-04 10:48:00.000  0    5
7   356     2011-05-04 11:14:00.000  1    1
8   356     2011-05-04 11:14:00.000  1    1
9   356     2011-05-04 11:15:00.000  1    1
10  356     2011-05-04 11:15:00.000  1    1
11  356     2011-05-04 11:15:00.000  1    1
12  356     2011-05-04 11:16:00.000  0    10
13  356     2011-05-04 11:16:00.000  0    10
14  356     2011-05-04 11:16:00.000  0    10
15  356     2011-05-04 14:49:00.000  1    4
16  356     2011-05-04 14:50:00.000  1    4
17  356     2011-05-04 14:50:00.000  1    4
18  356     2011-05-04 14:51:00.000  1    4
19  356     2011-05-04 14:52:00.000  0    14
20  356     2011-05-04 14:52:00.000  0    14
21  356     2011-05-04 20:52:00.000  0    14

You can see for yourself how every sequence of events with identical ign can now be easily distinguished from the others by its own key of (UnitId, ign, Grp). So now we can rank every sequence as well as every event within a sequence, which is what the ranked CTE does. It produces the following result set:

Id  UnitId  eventtime                ign  Grp  seqRank  eventRank
--  ------  -----------------------  ---  ---  -------  ---------
1   356     2011-05-04 10:41:00.000  1    0    1        1
2   356     2011-05-04 10:42:00.000  1    0    1        2
3   356     2011-05-04 10:43:00.000  1    0    1        3
4   356     2011-05-04 10:45:00.000  1    0    1        4
5   356     2011-05-04 10:47:00.000  1    0    1        5
6   356     2011-05-04 10:48:00.000  0    5    1        1
7   356     2011-05-04 11:14:00.000  1    1    2        1
8   356     2011-05-04 11:14:00.000  1    1    2        2
9   356     2011-05-04 11:15:00.000  1    1    2        3
10  356     2011-05-04 11:15:00.000  1    1    2        4
11  356     2011-05-04 11:15:00.000  1    1    2        5
12  356     2011-05-04 11:16:00.000  0    10   2        1
13  356     2011-05-04 11:16:00.000  0    10   2        2
14  356     2011-05-04 11:16:00.000  0    10   2        3
15  356     2011-05-04 14:49:00.000  1    4    3        1
16  356     2011-05-04 14:50:00.000  1    4    3        2
17  356     2011-05-04 14:50:00.000  1    4    3        3
18  356     2011-05-04 14:51:00.000  1    4    3        4
19  356     2011-05-04 14:52:00.000  0    14   3        1
20  356     2011-05-04 14:52:00.000  0    14   3        2
21  356     2011-05-04 20:52:00.000  0    14   3        3

You can see that an ign=1 sequence can now be matched with an ign=0 sequence with the help of seqRank. And picking only the earliest event from every sequence (filtering by eventRank=1) we'll get start and end times of all the ign=1 sequences. And so the result of the final CTE is:

UnitId  EventStart               EventEnd
------  -----------------------  -----------------------
356     2011-05-04 10:41:00.000  2011-05-04 10:48:00.000
356     2011-05-04 11:14:00.000  2011-05-04 11:16:00.000
356     2011-05-04 14:49:00.000  2011-05-04 14:52:00.000

Obviously, if the last ign=1 sequence isn't followed by an ign=0 event, it will not be shown in the final results, because the last ign=1 sequence will have no matching ign=0 sequence, using the above approach.

There's one possible case when this query will not work as it is. It's when the event list starts with an ign=0 event instead of ign=1. If that is actually possible, you could simply add the following filter to the ranked CTE:

WHERE NOT (ign = 0 AND Grp = 0)
-- Alternatively: WHERE ign <> 0 OR Grp <> 0

It takes advantage of the fact that the first value of Grp will always be 0. So, if 0 is assigned to events with ign=0, those events should be excluded.

---

Useful reading:

• WITH common_table_expression (Transact-SQL)

• Ranking Functions (Transact-SQL)

  • DENSE_RANK (Transact-SQL)

  • ROW_NUMBER (Transact-SQL)

Answer 2

You can join your own table based on row_number()

select t1.RowNo as t1Row, t2.RowNo as t2Row, t1.StarTime, t2.EndTime as EndTime, cast(t2.EndTime - t1.StarTime as time) as Duration, t1.ign as IgnPeriod
from (select eventtime as StarTime, ign, ROW_NUMBER() over (order by eventtime) as RowNo from UnitTable)  as t1
inner join (select * from (select eventtime as EndTime, ROW_NUMBER() over (order by eventtime) as RowNo from UnitTable) as t_inner) t2 on t1.RowNo + 1 = t2.RowNo

You can then sum the duration for the ign=1 periods:

select cast(dateadd(millisecond,sum(datediff(millisecond,0,duration)),0) as time) from 
(select cast(t2.EndTime - t1.StarTime as time) as Duration, t1.ign as IgnPeriod
from (select eventtime as StarTime, ign, ROW_NUMBER() over (order by eventtime) as RowNo from UnitTable)  as t1
inner join (select * from (select eventtime as EndTime, ROW_NUMBER() over (order by eventtime) as RowNo from UnitTable) as t_inner) t2 on t1.RowNo + 1 = t2.RowNo
) t_outer
where t_outer.IgnPeriod = 1

Answer 3

It's getting late, so rather than write out all the code this time I'm just gonna summarize how to do this:

Join the table to itself where one of the join conditions for the right hand side is a correlated sub query that selects the top 1 min(eventtime) from the table that is > the current row from the left hand side. Then use a where clause to filter this to rows where the ign column on the left side is <> to the ign column on the right side. That gets you your changes. Use a similar technique to join back to the table a third time, and you'll have records that have both a start time and end time that you can use with date functions to get your minute values.