날짜기준으로 빈데이터 조회 방법 0 6 4,086

by 이하성 [Oracle 기초] oracle query [2022.07.06 16:30:43]


캘린더 테이블, 근태 테이블, 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시간   으로 구성되어 있습니다.

by 마농 [2022.07.06 16:38:47]

각 테이블의 날짜컬럼은 자료형이 뭔가요? 저장포멧은?
조회 대상은 1명인가요? 여러명 인가요?
하루에 근태가 여러번 겹치는 경우는 없는지? (예 : 오전 출장, 오휴 휴가)


by 이하성 [2022.07.06 16:55:45]

각 테이블의 날짜 컬럼은 모두 date 형입니다.

전체 직원이 조회 대상이면 조건값으로 1명조회도 가능합니다.

근태는 한날짜에 한가지만 입력 가능합니다.(겹치는 경우는 발생되지 않습니다.)


by Race [2022.07.06 16:58:54]
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일 해당 월의 마지막 날짜까지 출력하는 쿼리 입니다.

해당 쿼리와 근태테이블과 아웃터 조인을 하시면 근태 등록하지 않은 날짜로 표시가 가능할 것 같습니다.


by 이하성 [2022.07.06 18:07:10]

감사합니다.~


by 마농 [2022.07.06 17:08:40]
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
;

 


by 이하성 [2022.07.06 18:07:27]

해결의 실마리를 찾았습니다.

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입