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?