Skip to content
Advertisement

Merge every 2 consecutive records into 1

I have a pre-processed table which I want to group every pair into one record containing data from fields of both records.

|-------------------|-----|----|
|Timestamp          |Event|User|
|-------------------|-----|----|
|17/03/2020 03:22:00|Start|1   |
|17/03/2020 03:22:05|End  |1   |
|17/03/2020 03:22:10|Start|2   |
|17/03/2020 03:22:15|End  |2   |
|17/03/2020 03:23:00|Start|1   |
|17/03/2020 03:23:22|End  |1   |
|-------------------|-----|----|

The query should return:

|-------------------|-------------------|----|
|StartTimestamp     |EndTimestamp       |User|
|-------------------|-------------------|----|
|17/03/2020 03:22:00|17/03/2020 03:22:05|1   |
|17/03/2020 03:22:10|17/03/2020 03:22:15|2   |
|17/03/2020 03:23:00|17/03/2020 03:23:22|1   |
|-------------------|-------------------|----|

You can safely assume that every 2 records is the correct pair (events are Start and End respectively, and User is the same) since the table is pre-filtered.

EDIT: Sorry, I forgot to mention that having multiple pairs for a single user is allowed. I’ve adjusted the example table above to show that.

Advertisement

Answer

As suggested, this should do what you want :

SELECT
     MIN(Timestamp) AS StartTimestamp,
     MAX(Timestamp) AS EndTimestamp,
     User
FROM 
     mytable
GROUP BY User;

EDIT : As a user id can appear multiple times, in multiple groups, see the following query :

WITH cte AS (
     SELECT mt.*, ROW_NUMBER() OVER(ORDER BY time) AS rn FROM mytable mt
)
SELECT 
     t1.userid,
     t1.time AS StartTimestamp, 
     t2.time AS EndTimestamp
FROM cte t1
JOIN cte t2 ON t1.rn+1 = t2.rn
WHERE t1.event = 'Start'

WITH DEMO HERE

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement