데이터베이스 질문과 답변
기간을 일자정보로 변경하는 쿼리 도움 부탁드립니다.
5
by 잼보야 [2014.04.10 09:56:19]
안녕하세요. 업무 중 쿼리가 안풀려서 도움을 요청드립니다.
사원번호별 휴일 시작일과 종료일을 관리하는 테이블과 회사 단체 휴일 정보를 관리하는 테이블이 있습니다.
휴일시작일과 종료일을 회사 단체 휴일 및 주말을 제외하고 날짜로 표시하고 싶은데 어떡게 해야될지 잘 모르겠어요.
휴일테이블 : WORK |
||
| 사번 | 휴일시작일 | 휴일종료일 |
| EMPNO | SDAY | EDAY |
| 1111 | 2014-04-01 | 2014-04-03 |
| 1111 | 2014-04-11 | 2014-04-14 |
| 2222 | 2014-04-23 | 2014-04-28 |
| 단체휴일정보 테이블 : HOLIDAY | ||
| 휴일 | ||
| HDAY | ||
| 2014-04-25 | ||
2014-04-12 : 토요일
2014-04-13 : 일요일
2014-04-25 : 단체휴일
2014-04-26 : 토요일
2014-04-27 : 일요일
-----표현하고 싶은 결과값 -------------------------------------------------------
| 사번 | 휴일정보 | ||||
| 1111 | 2014-04-01 | ||||
| 1111 | 2014-04-02 | ||||
| 1111 | 2014-04-03 | ||||
| 1111 | 2014-04-11 | ||||
| 1111 | 2014-04-14 | << 토요일, 일요일을 제외한 기간이 날짜로 표시 | |||
| 2222 | 2014-04-23 | ||||
| 2222 | 2014-04-24 | ||||
| 2222 | 2014-04-28 | << 토요일, 일요일, 회사단체휴일을 제외한 날짜가 표시 | |||
부탁드립니다.
by 농부지기
[2014.04.10 11:11:25]
복제할때 만 신경쓰셔서 SQL 작성하시면 되요
WITH TT(EMPNO, SDAY, EDAY) AS
( SELECT '1111', '20140401', '20140403' FROM DUAL UNION ALL
SELECT '1111', '20140411', '20140414' FROM DUAL UNION ALL
SELECT '2222', '20140423', '20140428' FROM DUAL
)
, HOL(HDAY) AS
( SELECT '20140412' FROM DUAL UNION ALL
SELECT '20140413' FROM DUAL UNION ALL
SELECT '20140425' FROM DUAL UNION ALL
SELECT '20140426' FROM DUAL UNION ALL
SELECT '20140427' FROM DUAL
)
--4. 휴일 제외 하기
SELECT TT.*
FROM (--3. 사원별 모든 휴무일 구하기
SELECT TT.EMPNO, TO_CHAR(TO_DATE(TT.SDAY, 'YYYYMMDD') + B.LV - 1, 'YYYYMMDD') HOLDAY
FROM (--2. 각 사원별 휴무일 구하기
SELECT EMPNO, SDAY, EDAY, TO_DATE(EDAY, 'YYYYMMDD') - TO_DATE(SDAY, 'YYYYMMDD') + 1 AS DCNT
FROM TT
) TT
, (--1. 먼저 자료를 복제 하기 위해서 가장먼저 수행.
SELECT LEVEL LV
FROM (
SELECT MAX(TO_DATE(EDAY, 'YYYYMMDD') - TO_DATE(SDAY, 'YYYYMMDD')) + 1 AS MAX_CNT
FROM TT
) B
CONNECT BY LEVEL <= B.MAX_CNT
) B
WHERE TT.DCNT >= B.LV
ORDER BY 1, 2
) TT
, HOL
WHERE TT.HOLDAY = HOL.HDAY(+)
AND HOL.HDAY IS NULL
ORDER BY EMPNO, TT.HOLDAY ;
by DarkBee
[2014.04.10 12:13:04]
WITH work (empno, sday, eday) AS (
SELECT '1111', '20140401', '20140403' FROM DUAL UNION ALL
SELECT '1111', '20140411', '20140414' FROM DUAL UNION ALL
SELECT '2222', '20140423', '20140428' FROM DUAL
)
, holiday (hday) AS (
SELECT '20140412' FROM DUAL UNION ALL
SELECT '20140413' FROM DUAL UNION ALL
SELECT '20140425' FROM DUAL UNION ALL
SELECT '20140426' FROM DUAL UNION ALL
SELECT '20140427' FROM DUAL
)
SELECT empno
, TO_DATE ( sday, 'YYYYMMDD' ) + lv - 1
, eday
, lv
FROM work a
, ( SELECT LEVEL lv
FROM DUAL
CONNECT BY LEVEL <= ( SELECT MAX ( TO_DATE ( eday ) - TO_DATE ( sday ) ) + 1
FROM work
)
) b
WHERE TO_DATE ( sday, 'YYYYMMDD' ) + lv - 1 <= TO_DATE ( eday, 'YYYYMMDD' )
AND NOT EXISTS ( SELECT 'X'
FROM holiday
WHERE TO_DATE ( a.sday, 'YYYYMMDD' ) + lv - 1 = hday
)
ORDER BY 1, 2
by 농부지기
[2014.04.10 12:29:52]
역시 level의 차이가 느껴 지네요.
by 잼보야
[2014.04.10 13:41:51]
너무 감사드립니다. 이틀 고민하다가 혼자 해결하려다 안되서 올렸는데 역시 많은 도움 주시네요. 감사드려요.
by 순자
[2014.04.10 14:24:48]
with t as (
select 1111 empno,'2014-04-01' sday,'2014-04-03' EDAY from dual union all
select 1111,'2014-04-11','2014-04-14' from dual union all
select 2222,'2014-04-23','2014-04-28' from dual
),h as (select '2014-04-25' hol from dual)
select empno,lv
from t, (select (to_date('2014-04-01','yyyy-mm-dd') -1 ) + level lv
from dual
connect by level < (to_date('2014-04-28','yyyy-mm-dd') - to_date('2014-04-01','yyyy-mm-dd')) +2) aa
where lv between sday and eday
and not exists (select 1 from h where aa.lv = h.hol)
and to_char(lv,'d') not in ('1','7')
order by empno, sday, lv
) 버튼을 클릭하여 작성 하시면 됩니다.