[ORACLE]휴일감안 전/후영업일자 조회

-- 기준일자와  휴일여부를 이용하여 전/전전/전전전, 후/후후/후후후 영업일자를 조회하기위한 sql

-- 인터넷 무쟈게 돌아댕겨도 전/후영업일자 조회하는 sql이 없어서 함. 만들어봤어요(내가 필요해서..ㅎㅎ)

-- dd: 특정일자, ck:2:비영업일, 1:영업일

with cal_t as(select '20110901' dd, '1' ck from dual union all
select '20110902' dd, '1' ck from dual union all
select '20110903' dd, '2' ck from dual union all
select '20110904' dd, '2' ck from dual union all
select '20110905' dd, '1' ck from dual union all
select '20110906' dd, '1' ck from dual union all
select '20110907' dd, '1' ck from dual union all
select '20110908' dd, '1' ck from dual union all
select '20110909' dd, '1' ck from dual union all
select '20110910' dd, '2' ck from dual union all
select '20110911' dd, '2' ck from dual union all
select '20110912' dd, '2' ck from dual union all
select '20110913' dd, '2' ck from dual union all
select '20110914' dd, '1' ck from dual union all
select '20110915' dd, '1' ck from dual union all
select '20110916' dd, '1' ck from dual union all
select '20110917' dd, '2' ck from dual union all
select '20110918' dd, '2' ck from dual union all
select '20110919' dd, '1' ck from dual union all
select '20110920' dd, '1' ck from dual union all
select '20110921' dd, '2' ck from dual union all
select '20110922' dd, '1' ck from dual union all
select '20110923' dd, '1' ck from dual union all
select '20110924' dd, '2' ck from dual union all
select '20110925' dd, '2' ck from dual union all
select '20110926' dd, '1' ck from dual union all
select '20110927' dd, '1' ck from dual union all
select '20110928' dd, '1' ck from dual union all
select '20110929' dd, '1' ck from dual union all
select '20110930' dd, '1' ck from dual
)
select a.dd
,a.ck
,case when a.ck = '1' then a.후영업일     else max(a.후영업일)     over(order by a.dd) end 후영업일
,case when a.ck = '1' then a.후후영업일   else max(a.후후영업일)   over(order by a.dd) end 후후영업일
,case when a.ck = '1' then a.후후후영업일 else max(a.후후후영업일) over(order by a.dd) end 후후후영업일
,case when a.ck = '1' then a.전영업일     else min(a.전영업일)     over(order by a.dd desc) end 전영업일
,case when a.ck = '1' then a.전전영업일   else min(a.전전영업일)   over(order by a.dd desc) end 전전영업일
,case when a.ck = '1' then a.전전전영업일 else min(a.전전전영업일) over(order by a.dd desc) end 전전전영업일  
from (
  select dd
  ,ck
  ,case when ck = '2' then null else lead(dd, 1) over(partition by ck order by dd) end 후영업일
  ,case when ck = '2' then null else lead(dd, 2) over(partition by ck order by dd) end 후후영업일
  ,case when ck = '2' then null else lead(dd, 3) over(partition by ck order by dd) end 후후후영업일  
  ,case when ck = '2' then null else lag(dd, 1)  over(partition by ck order by dd) end 전영업일
  ,case when ck = '2' then null else lag(dd, 2)  over(partition by ck order by dd) end 전전영업일
  ,case when ck = '2' then null else lag(dd, 3)  over(partition by ck order by dd) end 전전전영업일
  from cal_t
  where 1=1
 ) a
 order by a.dd

[출처] 휴일감안 전/후영업일자 조회|작성자 마중물

====================================================================뷰테이블 만들어서 사용

CREATE OR REPLACE FORCE VIEW APLUSORA.APLUS_V_WORK_DAY
(
   DD,
   ISWORK,
   B_DD

)
AS
     WITH CAL_T AS
(
    SELECT WORK_DAY DD, ISWORK FROM IFT_WORKING_DAY
    WHERE  WORK_DAY BETWEEN '20120700' AND '20121231'
)
SELECT A.DD
,A.ISWORK
,CASE WHEN A.ISWORK = 'Y' THEN DD ELSE MIN(A.B_DD)     OVER(ORDER BY A.DD DESC) END B_DD
FROM (
  SELECT DD
  ,ISWORK
  ,CASE WHEN ISWORK = 'N' THEN NULL ELSE LAG(DD, 1)  OVER(PARTITION BY ISWORK ORDER BY DD) END B_DD
  FROM CAL_T
  WHERE 1=1
 ) A;

SELECT TXDATE,권유자,권유자변경일,사번,계좌명,펀드계좌번호,AAA,BBB,AAA-BBB AS CCC
FROM
(
    SELECT
      A.TXDATE
     --,LENGTH(RECOMMEND_ID)
     ,A.RECOMMEND_NAME 권유자
     ,A.RECOMM_DATE 권유자변경일
     ,A.RECOMMEND_ID 사번
     ,A.CUSTOMER_NAME 계좌명
     ,A.FUND_ACCOUNT_NO 펀드계좌번호
     ,NVL(A.PAYMENT_AMOUNT_TOTAL,0) AAA
     ,NVL((SELECT PAYMENT_AMOUNT_TOTAL FROM IFK_FUND_RECOMMENDER_141
           WHERE TXDATE = CASE WHEN A.RECOMM_DATE-1 < '20120706'  THEN '20120706'
           ELSE (SELECT DECODE(ISWORK,'Y',DD,B_DD) AS WORK FROM APLUSORA.APLUS_V_WORK_DAY
                    WHERE DD = TO_CHAR(TO_DATE(A.RECOMM_DATE)-1,'YYYYMMDD')) END 
           AND FUND_ACCOUNT_NO = A.FUND_ACCOUNT_NO),0) BBB
    FROM APLUSORA.IFK_FUND_RECOMMENDER_141 A
    WHERE LENGTH(A.RECOMMEND_ID) =6
    AND A.TXDATE BETWEEN '20120706' AND '20120831'
    --AND A.RECOMM_DATE='20091001'
    AND A.RECOMMEND_ID NOT IN ('090001','090002')
) X

====================================================================