SQL count number of time series events, with some some start or stop entries missing

Question

I have some start/stop events and I need to count the number of total events but sometimes a start or stop is missing, for example:

Time   Event
10:50   START
10:52   STOP
10:59   START
11:01   STOP
11:45   STOP

Count(Event) Where Event='START' Would return 2, I also need to count the missing START value, so the result should be 3. Any ideas on how this could be done? Thanks!

Is it possible for two start-stop periods to overlap? This is critical for finding unpaired start or stop events. — Bill Huang, Feb 22 '20 at 01:08
Yeah I guess that should be the case. I have clarified in my answer about the necessary constraints I think there should be. — Bill Huang, Feb 22 '20 at 02:03

Bill Huang · Answer 1 · 2020-02-22T02:02:07.813

Two constraints must be met to enable event counting.

Two START-STOP periods cannot overlap.
Two consecutive and chronologically ordered START and STOP event cannot be possibly originated from two different events, namely START+(missing TOP) and (missing START)+STOP.

It the conditions are met, a simple state machine can be implemented to detect the "missing" events. Such a row-by-row logic could (almost always) be implemented using the cursor syntax.

N.B. To exemplify the generality of the cursor method you can also see other answers A (update columns), B (a tedious algo) I made. The code structures are highly similar.

Test Dataset

use [testdb];
if OBJECT_ID('testdb..test') is not null
    drop table testdb..test;

create table test (
    [time] varchar(50),
    [event] varchar(50),
);

insert into test ([time], [event])
values ('10:50', 'START'),('10:52', 'STOP'),('10:59', 'START'),
       ('11:01', 'STOP'),('11:45', 'STOP'),('11:50', 'STOP'),('11:55', 'START');

select * from test;

Code

/* cursor variables */

-- storage for each row
declare @time varchar(50),
        @event varchar(50),
        @state int = 0,  -- state variable
        @count int = 0;  -- event count

-- open a cursor ordered by [time]
declare cur CURSOR local
for select [time], [event]
    from test
    order by [time]
open cur;

/* main loop */

while 1=1 BEGIN

    /* fetch next row and check termination condition */
    fetch next from cur 
        into @time, @event;

    -- termination condition
    if @@FETCH_STATUS <> 0 begin
        -- check unfinished START before exit
        if @state = 1
            set @count += 1;
        -- exit loop
        break;
    end

    /* program body */

    -- case 1. state = 0 (clear state)
    if @state = 0 begin
        -- 1-1. normal case -> go to state 1
        if @event = 'START'
            set @state = 1;
        -- 1-2. a STOP without START -> keep state 0 and count++
        else if @event = 'STOP'
            set @count += 1;
        -- guard
        else 
            print '[Error] Bad event name: ' + @event
    end
    -- case 2. start = 1 (start is found)
    else if @state = 1 begin
        -- 2-1. normal case -> go to state 0 and count++
        if @event = 'STOP' begin
            set @count += 1;
            set @state = 0;            
        end
        -- 2-2. a START without STOP -> keep state 1 and count++
        else if @event = 'START'
            set @count += 1;
        -- guard
        else 
            print '[Error] Bad event name: ' + @event
    end
END

-- cleanup
close cur;
deallocate cur;

Result

print @count;  -- correct answer: 5

Tested on SQL Server 2017 (linux docker image, latest version).

Thanks @cbhuang the method you're showing has me thinking it could also potentially be done with a window function... Perhaps add a column (Next_Event) with the leading value, then add 1 if Event='Start' and Next_Event='Stop', and ALSO add 1 if Event=Next_Event. I think I'll try both ways and compare results. — Shawn Strasser, Feb 22 '20 at 03:17
In terms of I/O efficiency, the cursor method require only 1 pass of data (ignoring sorting which is required to be done anyway). Creating a Next_Event variable may have the potential to avoid cursor use, but the efficiency loss may be detrimental. This is also to be noted when your data is large. — Bill Huang, Feb 22 '20 at 03:29

score 0 · Answer 2 · answered Feb 22 '20 at 03:30

Well, you could count each start and then each "stop" where the preceding event is not a start:

select count(*)
from (select t.*,
             lag(event) over (order by time) as prev_event
      from t
     ) t
where event = 'start' or
      (prev_event = 'stop' and event = 'stop');

SQL count number of time series events, with some some start or stop entries missing

2 Answers2