# Merging two tables by minimal interval values

I have two tables with two columns keeping tabs of categorical values, such as:

``````Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+
``````

These Begin and End columns represent a continuous interval in which the value is valid. In the above example, the interval would be days in a month, so according to Table 1 I'd have a normal condition from day 1 to day 8, critical from day 8 to day 23, and normal again from the 23rd to the 30th. And I know from Table 2 that John was supervising from the 1st to the 14th, and Janice took over from the 14th to the 30th.

What I want is to merge these two tables, to have both Condition and Supervisor values in the same table, with the minimal interval for each pairing of values. So, this:

``````Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+
``````

What can be guaranteed of the tables is that:

• each will have its respective "Begin" and "End" fields.
• those will have the same span (the "Begin" value of the first row and the "End" value of the last row are the same for every table).
• that every interval is sequential (the "End" value of row n is always equal to the "Begin" value of row n+1).
• that the value of "End" will always be higher than "Begin" for a given row.

I had this worked out programatically in python, but I'm trying to scrap that script from my workflow and do everything directly in the DB. Ultimately I could replicate my python function in plpgsql, but I wonder if there is a more SQL-esque way of achieving this?

• DDL statements that allow us to re-create this would be helpful. Commented Mar 9, 2018 at 19:27
• See Darwen, Date & Lorentzos' book (now 2nd edition) on temporal databases. (Or Lorentzos's paper.) (Avoid Snodgrass's temporal work.) It's really a tutorial explanation, so there's not so much content as the page count would suggest. The keys are 6NF & interval NFs. However, although they explain the (elegant) semantics in terms of unpacking & packing to & from every point within an interval, which is how JackDouglas's answer here implements, it's possible to describe & implement using only the intervals that partition the timeline per input start & end points. More complex but fewer rows. Commented Mar 12, 2018 at 22:59

I'd approach this in three logical stages:

1. Expand the rows in both tables for all the days in the month and join them together.
2. Assign a 'group' to each series of rows where `condition` and `supervisor` don't change for a period (a "gaps and islands" problem).
3. Group the results.

So the solution looks like this:

``````create table t1(
id serial primary key
, begin_on integer
, end_on integer
, condition text
);

insert into t1(begin_on,end_on,condition)
values (1,8,'Normal')
, (8,23,'Critical')
, (23,30,'Normal');

create table t2(
id serial primary key
, begin_on integer
, end_on integer
, supervisor text
);

insert into t2(begin_on,end_on,supervisor)
values (1,14,'John')
, (14,30,'Janice');
``````
``````select min(g) begin_on, max(g)+1 end_on, condition, supervisor
from( select g
, condition
, supervisor
, row_number() over (order by g)
- row_number() over (partition by t1.id, t2.id order by g) grp
from generate_series(1,30) g
join t1 on g>=t1.begin_on and g<t1.end_on
join t2 on g>=t2.begin_on and g<t2.end_on ) z
group by condition, supervisor, grp
order by begin_on;
``````
```begin_on | end_on | condition | supervisor
-------: | -----: | :-------- | :---------
1 |      8 | Normal    | John
8 |     14 | Critical  | John
14 |     23 | Critical  | Janice
23 |     30 | Normal    | Janice
```

dbfiddle here

I am assuming this is a cut-down example of your real-world problem or I would also suggest changing the way you store the data in the first place, perhaps using date ranges instead of integers.

• Sorry for such a late response (shortly after I posted the question, was sent on a trip unable to look it up). Your answer works wonders! Yes, this was a cut-down example, I had to modify my data slightly to fit this MO, but it works very well, and very fast! Thanks a lot! Commented Mar 28, 2018 at 20:50