Присоединяйтесь, где пересекаются 2 периода времени

Question

У нас есть 2 таблицы, и мы пытаемся выяснить, как сделать перекрестное соединение, где пересекаются 2 периода времени.

Первая таблица (employment) содержит 4 столбца:

EmployerID, UserID, StartDate, EndDate

Второй стол (status_history) также содержит 4 столбца:

UserID, Status, StartDate, EndDate

employment таблица содержит записи, показывающие, с каким "заданием" был связан каждый пользователь и как долго (StartDate и EndDate). Так же, status_history содержит записи, показывающие, был ли пользователь активным / неактивным (занятым или безработным) - также с StartDate и EndDate.

Мы пытаемся создать представление, которое могло бы создать правильное "перекрестное соединение" между двумя таблицами.

EmployerID, UserID, Status, StartDate, EndDate

Я пытался создать SQL Fiddle, но по какой-то причине я получаю сообщение об ошибке. Итак, я предоставил схему ниже:

CREATE TABLE employment
    (`EmployerID` int, `UserID` int, `StartDate` date, `EndDate` date);

CREATE TABLE status_history
    (`UserID` int, `Status` varchar(10), `StartDate` date, `EndDate` date);

INSERT INTO employment
    (`EmployerID`, `UserID`, `StartDate`, `EndDate`)
VALUES
    (123, 111, '2017-01-01', '2017-03-04'),
    (345, 111, '2017-03-04', '2017-03-07'),
    (567, 111, '2017-03-07', '2017-04-10'),
    (789, 111, '2017-04-10', NULL)
;

INSERT INTO status_history
    (`UserID`, `Status`, `StartDate`, `EndDate`)
VALUES
    (111, 'Active', '2017-01-01', '2017-02-17'),
    (111, 'Inactive', '2017-02-17', '2017-03-02'),
    (111, 'Active', '2017-03-02', '2017-03-09'),
    (111, 'Inactive', '2017-03-09', NULL),
;

Основываясь на данных, я хочу получить следующие строки:

+------------+---------+-----------+-------------+-------------+
| EmployerID |  UserID |   Status  |  StartDate  |   EndDate   |
+------------+---------+-----------+-------------+-------------+
|        123 |     111 |  Active   |  2017-01-01 |  2017-02-17 |
|        123 |     111 |  Inactive |  2017-02-17 |  2017-03-02 |
|        123 |     111 |  Active   |  2017-03-02 |  2017-03-04 |
|        345 |     111 |  Active   |  2017-03-04 |  2017-03-07 |
|        567 |     111 |  Active   |  2017-03-07 |  2017-03-09 |
|        567 |     111 |  Inactive |  2017-03-09 |  2017-04-10 |
|        789 |     111 |  Inactive |  2017-04-10 |  NULL       |
+------------+---------+-----------+-------------+-------------+

Любая помощь приветствуется!

Answer 1

Поиграв с запросом, я смог придумать ответ.

Оказывается, ответ довольно прост: я получил результат, сделав соединение на UserID где период времени пересекается (т.е. StartDate от employment < EndDate от status_history а также EndDate от employment > StartDate от status_history, Для крайнего случая NULL EndDate я использую сегодняшнюю дату.

Затем я просто выбираю БОЛЬШОЙ из двух StartDate и МЕНЬШЕ из двух EndDate.