캘린더 테이블, 근태 테이블, PC On-Off 데이터 테이블을 가지고
매월 1일 ~ 말일 까지 날짜중 근태도 없고, PC On Off 데이터도 없는 날짜를 가져오고 싶습니다.
PC On-Off 테이블은 기록이 있을때 해당 날짜와 On Off 데이터가 입력이됩니다.
근태 테이블은 해당 근태가 등록이 되었을때 입력이 됩니다.
예를 들어, 6월 데이터를 기준으로 한다면
날짜 사번 성명 근태 PC ON PC OFF
6-1 1 홍길동 08:30 18:00
6-2 1 홍길동 연차
6-3 1 홍길동
6-4 1 홍길동 08:40 18:10
최종적으로 데이터들이 이렇게 표현이 되어지기를 원합니다.
추가적으로 보자면 색상이 칠해진 해당 데이터를 보고싶습니다.
참고로 근태 테이블의 경우
사번 성명 근태종류 근태시작일 근태종료일 로 구성되어 있고,
PC On-Off 테이블은
사번 성명 근무일 On시간 Off시간 으로 구성되어 있습니다.
SELECT TO_CHAR(FIRST_DAY - 1 + LEVEL, 'YYYYMMDD') DAY FROM (SELECT TO_DATE('202206', 'YYYYMM') FIRST_DAY FROM DUAL) CONNECT BY FIRST_DAY - 1 + LEVEL <= TRUNC(LAST_DAY(TO_DATE('202206', 'YYYYMM'))) ORDER BY DAY
위 쿼리는 6월 1일 해당 월의 마지막 날짜까지 출력하는 쿼리 입니다.
해당 쿼리와 근태테이블과 아웃터 조인을 하시면 근태 등록하지 않은 날짜로 표시가 가능할 것 같습니다.
WITH emp AS ( SELECT 1 eno, '홍길동' enm, '재직' st FROM dual UNION ALL SELECT 2, '구루비', '재직' FROM dual UNION ALL SELECT 3, '오라클클럽', '퇴직' FROM dual ) , calendar AS ( SELECT TO_DATE('202205', 'yyyymm') + LEVEL - 1 dt FROM dual CONNECT BY LEVEL <= 90 ) , time_t AS ( SELECT 1 eno, DATE '2022-06-02' dt, '연차' cd FROM dual ) , pc_on_off AS ( SELECT 1 eno, DATE '2022-06-01' dt, '08:30' pc_on, '18:00' pc_off FROM dual UNION ALL SELECT 1, DATE '2022-06-04', '08:40', '18:10' FROM dual ) SELECT a.eno , a.enm , b.dt , c.cd , d.pc_on , d.pc_off FROM emp a CROSS JOIN calendar b LEFT OUTER JOIN time_t c ON a.eno = c.eno AND b.dt = c.dt LEFT OUTER JOIN pc_on_off d ON a.eno = d.eno AND b.dt = d.dt WHERE b.dt >= TO_DATE('202206', 'yyyymm') AND b.dt < ADD_MONTHS(TO_DATE('202206', 'yyyymm'), 1) AND a.st = '재직' -- AND a.eno = 1 -- 특정 사원 검색 -- AND c.dt IS NULL AND d.dt IS NULL -- 근태 없는 날만 보기 ORDER BY a.eno, b.dt ;