두 기간의 중복되는 날짜 구하기

두 기간의 중복되는 날짜 구하기

교집합으로 겹치는 날짜를 구한다

SELECT DUP_STA_YMD
     , DUP_END_YMD
     , TO_DATE(DUP_END_YMD,'YYYYMMDD') - TO_DATE(DUP_STA_YMD,'YYYYMMDD') + 1 AS DUP_DAY_CNT
  FROM (SELECT :STD_STA_YMD AS STD_STA_YMD
             , :STD_END_YMD AS STD_END_YMD
             , :TRG_STA_YMD AS TRG_STA_YMD
             , :TRG_END_YMD AS TRG_END_YMD
             , CASE WHEN :STD_STA_YMD < :TRG_STA_YMD THEN :TRG_STA_YMD
                    ELSE :STD_STA_YMD
               END DUP_STA_YMD
             , CASE WHEN :STD_END_YMD < :TRG_END_YMD THEN :STD_END_YMD
                    ELSE :TRG_END_YMD
               END DUP_END_YMD
          FROM DUAL
         WHERE ((:STD_STA_YMD BETWEEN :TRG_STA_YMD AND :TRG_END_YMD OR :STD_END_YMD BETWEEN :TRG_STA_YMD AND :TRG_END_YMD)
             OR (:TRG_STA_YMD BETWEEN :STD_STA_YMD AND :STD_END_YMD AND :TRG_END_YMD BETWEEN :STD_STA_YMD AND :STD_END_YMD)
             )
     )
;