휴일 관리 feat.ORACLE DBMS
휴일 관리를 할때, 오라클 DBMS를 가지고 있을 경우 다음과 같은 형식의 테이블을 기반으로 처리 하며 쉽다!
테이블 구조
일반적으로 다음과 같은 데이터 구조를 가지고 있다고 생각하자
CREATE TABLE HOLIDAY (
SEQ NUMBER PRIMARY KEY, -- 일련번호
DT DATE, -- 일자
WEEK_NM VARCHAR(50), -- 요일명
HOLI_NM VARCHAR(50), -- 휴일명
HOLI_YN VARCHAR(1), -- 휴일 여부
REG_DATE DATE, -- 등록자
REG_USER VARCHAR(50), -- 등록일
REG_DATE DATE, -- 수정자
REG_USER VARCHAR(50) -- 수정일
)
휴일 구하는 SQL
함께 일하는 분이 알려주신 SQL, 복잡한듯 쉬운듯 하다.
/*특정일(&stdDate) 기준 N영업일수(&dayCnt) 전/후 영업일 구하기*/
SELECT MAX(DT) AS DT
FROM (SELECT /*+ INDEX_ASC(CCC(DT)) */
MAX(TO_CHAR(CCC.DT, 'YYYYMMDD')) AS DT
FROM HOLIDAY CCC
WHERE TO_NUMBER(&dayCnt) > 0
AND CCC.DT > TO_DATE(&stdDate, 'YYYYMMDD')
AND (CASE WHEN CCC.WEEK_NM IN ('Sat', 'Sun') THEN 'Y'
ELSE CCC.HOLI_YN
END) = 'N'
AND ROWNUM <= (SELECT MAX(LEVEL)
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER(&dayCnt))
UNION ALL
SELECT MAX(TO_CHAR(CCC.DT, 'YYYYMMDD')) AS DT
FROM HOLIDAY CCC
WHERE TO_NUMBER(&dayCnt) = 0
AND CCC.DT = TO_DATE(&stdDate, 'YYYYMMDD')
UNION ALL
SELECT /*+ INDEX_DESC(CCC(DT)) */
MIN(TO_CHAR(CCC.DT, 'YYYYMMDD')) AS DT
FROM HOLIDAY CCC
WHERE TO_NUMBER(&dayCnt) < 0
AND CCC.DT < TO_DATE(&stdDate, 'YYYYMMDD')
AND (CASE WHEN CCC.WEEK_NM IN ('Sat', 'Sun') THEN 'Y'
ELSE CCC.HOLI_YN
END) = 'N'
AND ROWNUM <= (SELECT MAX(LEVEL)
FROM DUAL
CONNECT BY LEVEL <= ABS(TO_NUMBER(&dayCnt)))
);
/*두 날짜사이(&frDt ~ &toDt) 영업일수 구하기*/
SELECT MAX(WOKING_DDCNT) AS WOKING_DDCNT
FROM (SELECT MAX(WOKING_DDCNT) AS WOKING_DDCNT
FROM (SELECT COUNT(*) AS WOKING_DDCNT
FROM HOLIDAY CCC
WHERE CCC.DT >= TO_DATE(TO_CHAR(TO_DATE(&frDt, 'YYYYMMDD') 1, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND CCC.DT <= TO_DATE(TO_CHAR(TO_DATE(&toDt, 'YYYYMMDD') , 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS')
AND (CASE WHEN CCC.WEEK_NM IN ('Sat', 'Sun') THEN 'Y'
ELSE CCC.HOLI_YN
END) = 'N')
WHERE TO_DATE(&frDt, 'YYYYMMDD') < TO_DATE(&toDt, 'YYYYMMDD')
UNION ALL
SELECT MAX(WOKING_DDCNT) * -1 AS WOKING_DDCNT
FROM (SELECT COUNT(*) AS WOKING_DDCNT
FROM HOLIDAY CCC
WHERE CCC.DT >= TO_DATE(TO_CHAR(TO_DATE(&frDt, 'YYYYMMDD') 1, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND CCC.DT <= TO_DATE(TO_CHAR(TO_DATE(&toDt, 'YYYYMMDD') , 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS')
AND (CASE WHEN CCC.WEEK_NM IN ('Sat', 'Sun') THEN 'Y'
ELSE CCC.HOLI_YN
END) = 'N')
WHERE TO_DATE(&frDt, 'YYYYMMDD') > TO_DATE(&toDt, 'YYYYMMDD')
UNION ALL
SELECT 0 AS WOKING_DDCNT
FROM DUAL
WHERE TO_DATE(&frDt, 'YYYYMMDD') = TO_DATE(&toDt, 'YYYYMMDD')
);