CODEWITHSUNDEEP
sqlsql-server
Freddy
Freddy

Reputation: 11

Finding Start and Stop events in the log table

There is a table with start and stop logs.Between start and stop, there may be other different events that are not known in advance.

It is necessary to output in a separate field so that from start to stop was for example 1, and from stop to start 0

DECLARE @Mytable TABLE (
  [EventId] [int] IDENTITY(1,1) ,
  [EventName] varchar(64) ,
  [EventTime] datetime NOT NULL
)

INSERT INTO @Mytable
VALUES
('Start','2019-08-31 13:09:48.000'),
('EVENT A','2019-08-31 13:10:32.000'),
('EVENT B','2019-08-31 15:45:04.000'),
('EVENT B','2019-08-31 15:51:09.000'),
('EVENT A','2019-08-31 15:55:41.000'),
('EVENT C','2019-08-31 15:57:14.000'),
('Stop','2019-08-31 15:58:12.000'),
('EVENT D','2019-08-31 16:17:04.000'),
('EVENT E','2019-08-31 16:19:09.000'),
('EVENT W','2019-08-31 16:21:41.000'),
('EVENT Y','2019-08-31 16:27:14.000'),
('Start','2019-08-31 16:30:48.000')

SELECT * FROM @Mytable ORDER BY [EventTime]

My expectation is

EventId EventName   EventTime            Ex
1           Start           2019-08-31 13:09:48.000  1
2           EVENT A         2019-08-31 13:10:32.000  1
3           EVENT B         2019-08-31 15:45:04.000  1
4           EVENT B         2019-08-31 15:51:09.000  1
5           EVENT A         2019-08-31 15:55:41.000  1
6           EVENT C         2019-08-31 15:57:14.000  1
7           Stop            2019-08-31 15:58:12.000  0
8           EVENT D         2019-08-31 16:17:04.000  0
9           EVENT E         2019-08-31 16:19:09.000  0
10          EVENT W         2019-08-31 16:21:41.000  0
11          EVENT Y         2019-08-31 16:27:14.000  0
12          Start           2019-08-31 16:30:48.000  1

Upvotes: 0

Views: 331

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think of this as a cumulative sum, with "1"s for starts and "-1"s for stops:

select t.*,
       sum(case when eventname = 'Start' then 1
                when eventname = 'Stop' then -1
                else 0
           end) over (order by eventtime) as ex
from @mytable t
order by eventtime;

Here is a db<>fiddle.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

We could try using window functions here:

SELECT
    EventId,
    EventName,
    EventTime,
    CASE WHEN COUNT(CASE WHEN EventName = 'Start' THEN 1 END) OVER (ORDER BY EventTime) >
              COUNT(CASE WHEN EventName = 'Stop' THEN 1 END) OVER (ORDER BY EventTime)
         THEN 1 ELSE 0 END AS Ex
FROM @Mytable
ORDER BY
    EventTime;

The trick here is that the first count of Start should be greater than the End count by one when we are inside a block which has begun but not yet ended. Then, when the block is ended, there should be an equal number of preceding Start and Stop.

Upvotes: 0

Related Questions