by 뽀꼼여사 [SQL Query] QUERY ORACLE SQL [2017.09.01 14:18:09]
일별 휴가간 인원 COUNT하기
DATA
이름 | 시작 | 끝 |
강단이 | 8/1 | 8/5 |
박지훈 | 8/5 | 8/7 |
이대휘 | 8/1 | 8/3 |
김재환 | 8/4 | 8/6 |
결과값
날짜 | 인원 |
8/1 | 2 |
8/2 | 2 |
8/3 | 2 |
8/4 | 2 |
8/5 | 3 |
8/6 | 2 |
8/7 | 1 |
/* FRDT : 20170801 TODT : 20170806 */ WITH DATE_T AS ( SELECT TO_DATE( :FRDT , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL CONNECT BY LEVEL <= TO_DATE(:TODT , 'YYYYMMDD') - TO_DATE(:FRDT, 'YYYYMMDD') + 1 ) , EMP_T AS ( SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL SELECT '박지훈' , '20170805' , '20170807' FROM DUAL UNION ALL SELECT '이대휘' , '20170801' , '20170803' FROM DUAL UNION ALL SELECT '김재환' , '20170804' , '20170806' FROM DUAL ) SELECT A.DT , COUNT(NM) CNT FROM DATE_T A ,EMP_T B WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT GROUP BY DT ORDER BY DT
WITH DATE_T AS (
SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20170801' , 'YYYYMMDD') - TO_DATE('20170806', 'YYYYMMDD') + 1 ) , EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈' , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘' , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환' , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
FROM DATE_T A
,EMP_T B
WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT
GROUP BY DT
ORDER BY DT
아, 제가 날짜를 반대로 넣었었네여^^;;
감사합니다~!!!!
아,, 0인 날짜는 안나와요~~
이거 어떻게 하면 될까요~??
WITH DATE_T AS (
SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20170810' , 'YYYYMMDD') - TO_DATE('20170801', 'YYYYMMDD') + 1 ),--LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE-1),-3))+1-2/24) ,
EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈' , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘' , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환' , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
FROM DATE_T A
,EMP_T B
WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT
GROUP BY DT
ORDER BY DT
아~ 알려주신대로 해봤어요~~
이렇게 하면 될까요~?
WITH DATE_T AS (
SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20170810' , 'YYYYMMDD') - TO_DATE('20170801', 'YYYYMMDD') + 1 ),
EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈' , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘' , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환' , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
FROM DATE_T A
,EMP_T B
WHERE A.DT BETWEEN B.ST_DT(+) AND B.ED_DT(+)
GROUP BY DT
ORDER BY DT
WITH emp_t AS ( SELECT '강단이' nm, '20170801' st_dt, '20170805' ed_dt FROM dual UNION ALL SELECT '박지훈', '20170805', '20170807' FROM dual UNION ALL SELECT '이대휘', '20170801', '20170803' FROM dual UNION ALL SELECT '김재환', '20170804', '20170806' FROM dual ) , date_t AS ( SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyymmdd') dt FROM (SELECT TO_DATE('20170801', 'yyyymmdd') sdt , TO_DATE('20170808', 'yyyymmdd') edt FROM dual ) CONNECT BY LEVEL <= edt - sdt + 1 ) SELECT a.dt , COUNT(b.nm) cnt FROM date_t a LEFT OUTER JOIN emp_t b ON a.dt BETWEEN b.st_dt AND b.ed_dt GROUP BY dt ORDER BY dt ;
다른 분들의 SQL을 많이 보고 연습하다보니,
SQL 구문이 비슷하게 만들어지네요..
WITH EMP_VACATION_M AS ( SELECT '강단이' EMP_NAME, '20170801' S_DATE, '20170804' E_DATE FROM DUAL UNION ALL SELECT '박지훈' , '20170806', '20170807' FROM DUAL UNION ALL SELECT '이대휘' , '20170801', '20170803' FROM DUAL UNION ALL SELECT '김재환' , '20170804', '20170804' FROM DUAL ), DATE_LIST AS ( SELECT S_DATE + (LEVEL - 1) AS VACATION_DATE FROM (SELECT MIN(TO_DATE(EMP_VACATION_M.S_DATE, 'YYYY-MM-DD')) S_DATE, MAX(TO_DATE(EMP_VACATION_M.E_DATE, 'YYYY-MM-DD')) E_DATE FROM EMP_VACATION_M ) TT CONNECT BY LEVEL<= TT.E_DATE - TT.S_DATE + 1 ) SELECT B.VACATION_DATE, COUNT(A.EMP_NAME) AS EMP_COUNT FROM EMP_VACATION_M A RIGHT OUTER JOIN DATE_LIST B ON B.VACATION_DATE BETWEEN TO_DATE(A.S_DATE, 'YYYY-MM-DD') AND TO_DATE(A.E_DATE, 'YYYY-MM-DD') GROUP BY B.VACATION_DATE ORDER BY B.VACATION_DATE