複数の履歴管理されるマスタを結合してマスタを作る(外部結合編)
前々回の記事にて、内部結合を使って複数の履歴管理されるマスタを結合する方法を書いたが、マスタを外部結合する必要がある場合、困ったことになる。外部結合では下図のように空き領域を埋めなければ、その部分の開始日、終了日が取得できない。
今のところ、簡単な方法は見つかっていないが、次のように隙間を埋めたマスタを結合することで外部結合でも履歴管理されたマスタを作ることができる。
WITH SM AS ( SELECT SMT.組織コード, SMT.開始日, NVL(LEAD(SMT.開始日) OVER ( PARTITION BY SMT.組織コード, ORDER BY SMT.開始日 ) -1, TO_DATE('9999-12-31')) AS 終了日, SM.組織名称 FROM ( SELECT SM.組織コード, TO_DATE('1900-01-01') AS 開始日 FROM 組織マスタ SM UNION SELECT SM.組織コード, SM.開始日 FROM 組織マスタ SM UNION SELECT SM.組織コード, SM.終了日 + 1 AS 開始日 FROM 組織マスタ SM ) SMT LEFT JOIN 組織マスタ SM ON SM.組織コード = SMT.組織コード AND SM.開始日 = SMT.開始日 ) SELECT IM.社員コード, SM.組織コード, GREATEST( IM.開始日, NVL(SM.開始日, TO_DATE('1900-01-01')) ) AS 開始日, LEAST( IM.終了日, NVL(SM.終了日, TO_DATE('9999-12-31')) ) AS 終了日, IM.社員名称, SM.組織名称 FROM 社員マスタ IM LEFT JOIN SM ON SM.組織コード = IM.組織コード AND SM.開始日 <= IM.終了日 AND SM.終了日 >= IM.開始日
[追記] SQLに不備があったため修正しました。
※もし、もっと簡単な方法があったら教えてください。