근태 내역 조회

근태 내역을 조회하는 쿼리를 작성해보자.

예제를 위해 아래와 같이 근태 테이블을 생성하자.

-- 1
DROP TABLE 근태 PURGE;
CREATE TABLE 근태 (출근일시 DATE, 퇴근일시 DATE);

INSERT INTO 근태 VALUES (TO_DATE ('201407010900', 'YYYYMMDDHH24MI'), TO_DATE ('201407011800', 'YYYYMMDDHH24MI'));
INSERT INTO 근태 VALUES (TO_DATE ('201407020700', 'YYYYMMDDHH24MI'), TO_DATE ('201407021230', 'YYYYMMDDHH24MI'));
INSERT INTO 근태 VALUES (TO_DATE ('201407030930', 'YYYYMMDDHH24MI'), TO_DATE ('201407032300', 'YYYYMMDDHH24MI'));
INSERT INTO 근태 VALUES (TO_DATE ('201407041230', 'YYYYMMDDHH24MI'), TO_DATE ('201407051330', 'YYYYMMDDHH24MI'));
INSERT INTO 근태 VALUES (TO_DATE ('201407051230', 'YYYYMMDDHH24MI'), TO_DATE ('201407052330', 'YYYYMMDDHH24MI'));
COMMIT;

 

아래는 근태 테이블을 조회한 결과다.

-- 2
SELECT * FROM 근태 ORDER BY 1;

출근일시         퇴근일시
---------------- ----------------
2014-07-01 09:00 2014-07-01 18:00
2014-07-02 07:00 2014-07-02 12:30
2014-07-03 09:30 2014-07-03 23:00
2014-07-04 13:45 2014-07-05 03:00
2014-07-05 12:30 2014-07-05 13:30
2014-07-06 12:30 2014-07-06 23:30

6 개의 행이 선택되었습니다.

 

근태가 아래와 같이 관리된다고 가정하자. 가정에 따라 근무 시간에 해당하는 근무일자 테이블을 생성하자.

• 근무시간은 당일 6:00부터 익일 2:00까지며, 중식(12:00~13:00)과 석식(18:00~19:00) 시간은 근무시간으로 인정하지 않는다.
• 평일의 정규 근무시간은 8시간이며 초과 근무시간은 최대 4시간이고, 주말의 초과 근무시간은 최대 6시간이다.

-- 3
DROP TABLE 근무일자 PURGE;

CREATE TABLE 근무일자
AS
SELECT a.기준일자
     , a.기준일자 + b.시작시간 AS 시작일시
     , a.기준일자 + b.종료시간 AS 종료일시
  FROM (SELECT     DATE '2014-07-01' + (LEVEL - 1) AS 기준일자
              FROM DUAL
        CONNECT BY LEVEL <= EXTRACT (DAY FROM LAST_DAY (DATE '2014-07-01'))) a
     , (SELECT     DECODE (LEVEL, 1, 06, 2, 13, 3, 19) / 24 AS 시작시간
                 , DECODE (LEVEL, 1, 12, 2, 18, 3, 26) / 24 AS 종료시간
              FROM DUAL
        CONNECT BY LEVEL <= 3) b;

 

아래는 근무일자 테이블을 조회한 결과다.

SELECT * FROM 근무일자 ORDER BY 1, 2;

기준일자   시작일시            종료일시
---------- ------------------- -------------------
2014-07-01 2014-07-01 06:00:00 2014-07-01 12:00:00
2014-07-01 2014-07-01 13:00:00 2014-07-01 18:00:00
2014-07-01 2014-07-01 19:00:00 2014-07-02 02:00:00
...
93 개의 행이 선택되었습니다.

 

아래의 쿼리로 정규 근무시간과 초과 근무시간을 조회할 수 있다.

-- 4
SELECT   a.출근일시
       , a.퇴근일시
       , a.기준일자
       , LEAST (a.근무시간, a.정규시간) AS 정규근무시간
       , LEAST (GREATEST (a.근무시간 - a.정규시간, 0), a.초과시간) AS 초과근무시간
    FROM (SELECT   a.기준일자
                 , a.출근일시
                 , a.퇴근일시
                 , CASE WHEN TO_CHAR (a.기준일자, 'D') IN ('1', '7') THEN 0 ELSE 8 END AS 정규시간
                 , CASE WHEN TO_CHAR (a.기준일자, 'D') IN ('1', '7') THEN 6 ELSE 4 END AS 초과시간
                 , ROUND (SUM ((a.최종일시 - a.최초일시) * 24), 2) AS 근무시간
              FROM (SELECT a.출근일시
                         , a.퇴근일시
                         , GREATEST (a.출근일시, b.시작일시) AS 최초일시
                         , LEAST (a.퇴근일시, b.종료일시) AS 최종일시
                         , b.기준일자
                      FROM 근태 a
                         , 근무일자 b
                     WHERE b.시작일시 < a.퇴근일시
                       AND b.종료일시 > a.출근일시) a
          GROUP BY a.기준일자
                 , a.출근일시
                 , a.퇴근일시) a
ORDER BY a.사원번호
       , a.기준일자;

출근일시         퇴근일시         기준일자   정규근무시간 초과근무시간
---------------- ---------------- ---------- ------------ ------------
2014-07-01 09:00 2014-07-01 18:00 2014-07-01            8            0
2014-07-02 07:00 2014-07-02 12:30 2014-07-02            5            0
2014-07-03 09:30 2014-07-03 23:00 2014-07-03            8          3.5
2014-07-04 13:45 2014-07-05 03:00 2014-07-04            8         3.25
2014-07-05 12:30 2014-07-05 13:30 2014-07-05            0          0.5
2014-07-06 12:30 2014-07-06 23:30 2014-07-06            0            6

6 개의 행이 선택되었습니다.