안녕하세요.
외박신청을 받는 테이블이 있는데
한달에 제한된 일수를 초과할 수 없도록
기존 신청한 자료에서 해당 월의 외박일수를 구할려고 합니다.
WITH t AS ( SELECT '2016-08-30' start_date, '2016-09-02' end_date FROM dual UNION ALL SELECT '2016-09-11', '2016-09-11' FROM dual UNION ALL SELECT '2016-09-14', '2016-09-15' FROM dual ) SELECT * from t
위 데이타에서 9월의 외박일수는 5일입니다.
(9월1일,2일,11일,14일,15일)
쿼리로 외박일수 5를 구하는게 가능할까요?
도움 부탁드립니다
with t as ( select '2016-08-30' start_date, '2016-09-02' end_date from dual union all select '2016-09-11', '2016-09-11' from dual union all select '2016-09-14', '2016-09-15' from dual ) select count(lv) cnt from ( select * from t,(select to_date('2016-08-30','yyyy-mm-dd') + level - 1 lv from dual connect by level <= (to_date('2016-09-15','yyyy-mm-dd') - to_date('2016-08-30','yyyy-mm-dd')) + 1) b where t.start_date <= b.lv and t.end_date >= b.lv) where to_char(lv,'yyyymm') = '201609';
대충 이렇게하면 될꺼 같은데 참고 하시길...
WITH t AS
(
SELECT '2016-08-30' start_date, '2016-09-02' end_date FROM dual
UNION ALL
SELECT '2016-09-11', '2016-09-11' FROM dual
UNION ALL
SELECT '2016-09-14', '2016-09-15' FROM dual
)
SELECT SUM(LEAST(TO_DATE(END_DATE, 'YYYY-MM-DD'), TO_DATE('2016-09-30', 'YYYY-MM-DD')) - GREATEST(TO_DATE(START_DATE, 'YYYY-MM-DD'), TO_DATE('2016-09-01', 'YYYY-MM-DD')) + 1)
from t
한문제에 대해서 다양한 해결책들이 제시 되서 재밌네요....ㅎㅎ
저도 한번 :DT 에는 구하고자 하는 월을 'YYYY-MM' 형식으로 입력
WITH T AS ( SELECT '2016-08-30' START_DATE, '2016-09-02' END_DATE FROM DUAL UNION ALL SELECT '2016-09-11', '2016-09-11' FROM DUAL UNION ALL SELECT '2016-09-14', '2016-09-15' FROM DUAL ) SELECT SUM(END_DATE - START_DATE + 1) FROM ( SELECT CASE WHEN TO_DATE(START_DATE, 'yyyy-mm-dd') < TO_DATE(:DT || '-01', 'yyyy-mm-dd') THEN TO_DATE(:DT || '-01', 'yyyy-mm-dd') ELSE TO_DATE(START_DATE, 'yyyy-mm-dd') END START_DATE, CASE WHEN TO_DATE(END_DATE, 'yyyy-mm-dd') > LAST_DAY(TO_DATE(:DT, 'yyyy-mm')) THEN LAST_DAY(TO_DATE(:DT, 'yyyy-mm')) ELSE TO_DATE(END_DATE, 'yyyy-mm-dd') END END_DATE FROM T ) WHERE TO_CHAR(START_DATE, 'yyyy-mm') = :DT OR TO_CHAR(END_DATE, 'yyyy-mm') = :DT
쿼리에 논리적 오류가 있었네요..ㅠㅠ
수정본 입니다.
WITH T AS ( SELECT '2016-08-30' START_DATE, '2016-09-02' END_DATE FROM DUAL UNION ALL SELECT '2016-09-11', '2016-09-11' FROM DUAL UNION ALL SELECT '2016-09-14', '2016-09-15' FROM DUAL UNION ALL SELECT '2016-10-14', '2016-10-15' FROM DUAL ) SELECT SUM(END_DATE - START_DATE + 1) FROM ( SELECT CASE WHEN TO_DATE(START_DATE, 'yyyy-mm-dd') < TO_DATE(:DT || '-01', 'yyyy-mm-dd') and TO_DATE(END_DATE, 'yyyy-mm-dd') > TO_DATE(:DT || '-01', 'yyyy-mm-dd') THEN TO_DATE(:DT || '-01', 'yyyy-mm-dd') ELSE TO_DATE(START_DATE, 'yyyy-mm-dd') END START_DATE, CASE WHEN TO_DATE(END_DATE, 'yyyy-mm-dd') > LAST_DAY(TO_DATE(:DT, 'yyyy-mm')) and TO_DATE(START_DATE, 'yyyy-mm-dd') < LAST_DAY(TO_DATE(:DT, 'yyyy-mm')) THEN LAST_DAY(TO_DATE(:DT, 'yyyy-mm')) ELSE TO_DATE(END_DATE, 'yyyy-mm-dd') END END_DATE FROM T ) WHERE TO_CHAR(START_DATE, 'yyyy-mm') = :DT OR TO_CHAR(END_DATE, 'yyyy-mm') = :DT
WITH t AS ( SELECT 1 id, '2016-08-30' start_date, '2016-09-02' end_date FROM dual UNION ALL SELECT 1, '2016-09-11', '2016-09-11' FROM dual UNION ALL SELECT 1, '2016-09-14', '2016-09-15' FROM dual ) SELECT SUM( LEAST(TO_DATE(end_date, 'yyyy-mm-dd'), LAST_DAY(TO_DATE(:v_ym, 'yyyy-mm'))) - GREATEST(TO_DATE(start_date, 'yyyy-mm-dd'), TO_DATE(:v_ym, 'yyyy-mm')) + 1 ) cnt FROM t WHERE id = :v_id AND start_date <= :v_ym || '-31' AND end_date >= :v_ym || '-01' ;