CODEWITHSUNDEEP
mysqlsqlmerge
Shadi Al Barhouch
Shadi Al Barhouch

Reputation: 117

How to merge tables in standard sql92 or in mysql

Suppose I have the following set in a table:

empid start_time end_time
1 8 9
1 9 10
1 11 12
1 12 13
1 13 14
1 14 15

I want to have an sql (or an sql process ) that convert the previous set to the following set:

empid start_time end_time
1 8 10
1 11 15

It means that if the end_time of a record equals to the start_time of the next record we shall remove one record and update the record with the new value (of course without touching the main table)

Upvotes: 1

Views: 127

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a type of gaps-and-islands problem. In this case, you can use lag to see where an "island" starts, then use a cumulative sum to assign the same number within an island and aggregate:

select empid, min(start_time), max(end_time)
from (select t.*,
             sum(case when prev_end_time = start_time then 0 else 1 end) over (partition by empid order by start_time) as island
      from (select t.*,
                   lag(end_time) over (partition by empid order by start_time) as prev_end_time
            from t
           ) t
     ) t
group by empid, island;

Here is a db<>fiddle.

Upvotes: 2

Related Questions