Алгоритм GREATEST-LEAST

Если возникает задача построения истории из двух и более таблиц содержащих историю изменений состояний, то можно воспользоваться алгоритмом соединения двух и более исторических таблиц GREATEST-LEAST. Алгоритм очень чувствительный к качеству истории соединяемых таблиц. Алгоритм очень легко понять на примере приведенном внизу. В примере история начинается с 1900 года и заканчивается 2999 годом. В определенных случаях если при соединении будет использоваться LEFT JOIN то как пример GREATEST будет выглядеть так
GREATEST(A.BEG_DT, NVL(B.BEG_DT, A.BEG_DT)) что в принципе тоже будет работать.


Реализация алгоритма на Oracle:

WITH T_MAIN AS
(SELECT 1 ID, 2 ID_F, 4 ID_C, DATE '1900-01-01' BEG_DT, DATE '2000-01-01' END_DT FROM DUAL UNION
 SELECT 1 ID, 3 ID_F, 4 ID_C, DATE '2000-01-01' BEG_DT, DATE '2010-01-01' END_DT FROM DUAL UNION
 SELECT 1 ID, 3 ID_F, 5 ID_C, DATE '2010-01-01' BEG_DT, DATE '2999-01-01' END_DT FROM DUAL
 ) ,
T_FIO AS
(SELECT 2 ID_F, 'Вася' FIO, DATE '1900-01-01' BEG_DT, DATE '2001-01-01' END_DT FROM DUAL UNION
 SELECT 2 ID_F, 'Петя' FIO, DATE '2001-01-01' BEG_DT, DATE '2999-01-01' END_DT FROM DUAL UNION
 SELECT 3 ID_F, 'Иван' FIO, DATE '1900-01-01' BEG_DT, DATE '2999-01-01' END_DT FROM DUAL 
) ,
T_CITY AS
(SELECT 4 ID_C, 'Москва' CITY, DATE '1900-01-01' BEG_DT, DATE '1990-01-01' END_DT FROM DUAL UNION
 SELECT 4 ID_C, 'Омск'   CITY, DATE '1990-01-01' BEG_DT, DATE '2999-01-01' END_DT FROM DUAL UNION
 SELECT 5 ID_C, 'Томск'  CITY, DATE '1900-01-01' BEG_DT, DATE '2999-01-01' END_DT FROM DUAL 
) 
SELECT T_MAIN.ID,
       T_FIO.FIO,
       T_CITY.CITY,
       GREATEST(T_MAIN.BEG_DT, T_FIO.BEG_DT, T_CITY.BEG_DT) AS BEG_DT,
       LEAST(T_MAIN.END_DT, T_FIO.END_DT, T_CITY.END_DT) AS END_DT
FROM   T_MAIN,
       T_FIO,
       T_CITY
WHERE  T_MAIN.ID_F = T_FIO.ID_F
       AND T_MAIN.ID_C = T_CITY.ID_C
       AND GREATEST(T_MAIN.BEG_DT, T_FIO.BEG_DT, T_CITY.BEG_DT) < LEAST(T_MAIN.END_DT, T_FIO.END_DT, T_CITY.END_DT)
ORDER  BY GREATEST(T_MAIN.BEG_DT, T_FIO.BEG_DT, T_CITY.BEG_DT);

В результате получим:

	ID	FIO	CITY	BEG_DT	END_DT
1	1	Вася	Москва	01.01.1900	01.01.1990
2	1	Вася	Омск	01.01.1990	01.01.2000
3	1	Иван	Омск	01.01.2000	01.01.2010
4	1	Иван	Томск	01.01.2010	01.01.2999