데이터베이스 질문과 답변
안녕하세요.
조언을 구하고자 글을 남깁니다.
attend테이블의 edate(날짜), time1(출근시간), time2(퇴근시간) 컬럼을 사용하여 A~D사람의 지정기간 동안의 총근무시간을 구하고자 합니다.
(추가질문 : 11/1 ~ 11/3까지 A,B,C의 총 근무시간을 구하고자함.)
ex)
| A | B | C | A | B | C | |
| edate | 20171103 | 20171103 | 20171103 | 20171101 | 20171101 | 20171101 |
| time1 | 065020 | 063027 | 065901 | 064632 | 070250 | 063011 |
| time2 | 154023 | 161010 | 160520 | 154923 | 180001 | 131111 |
조언 부탁드립니다.
by 우리집아찌
[2017.11.06 16:35:42]
data 샘플 좀 올려주세요.
위에 질문만 가지고는 모르겠습니다.
by 마농
[2017.11.07 09:28:23]
WITH attend AS
(
SELECT 'A' id, '20171103' edate, '065020' time1, '154023' time2 FROM dual
UNION ALL SELECT 'B', '20171103', '063027', '161010' FROM dual
UNION ALL SELECT 'C', '20171103', '065901', '160520' FROM dual
UNION ALL SELECT 'D', '20171103', '064632', '154923' FROM dual
)
-- 1. 퇴근시간이 자정을 넘기지 않는다고 가정.
SELECT id, edate, time1, time2
, TO_CHAR(TO_DATE(ROUND(
(TO_DATE(time2, 'hh24miss')- TO_DATE(time1, 'hh24miss'))*24*60*60
), 'SSSSS'), 'hh24miss') tm
FROM attend
;
WITH attend AS
(
SELECT 'A' id, '20171103' edate, '065020' time1, '154023' time2 FROM dual
UNION ALL SELECT 'B', '20171103', '063027', '161010' FROM dual
UNION ALL SELECT 'C', '20171103', '065901', '160520' FROM dual
UNION ALL SELECT 'D', '20171103', '064632', '154923' FROM dual
UNION ALL SELECT 'E', '20171103', '064632', '024923' FROM dual -- 요거
)
-- 2. 퇴근시간이 자정을 넘길수도 있다면?
SELECT id, edate, time1, time2
, TO_CHAR(TO_DATE(ROUND(
( TO_DATE(time2, 'hh24miss') - TO_DATE(time1, 'hh24miss')
+ CASE WHEN time1 > time2 THEN 1 ELSE 0 END
)*24*60*60
), 'SSSSS'), 'hh24miss') tm
FROM attend
;
-- 3. 퇴근시간이 하루(24시간)를 넘길수도 있다면?
-- - 위 모델로는 불가능.
by rotic
[2017.11.07 10:31:40]
답변 감사합니다.
추가적으로 기간을 설정하여 A,B,C 의 총 근무시간을 구할려고 합니다.
조언 부탁드려요
by 마농
[2017.11.07 13:04:24]
WITH attend AS
(
SELECT 'A' id, '20171103' edate, '065020' time1, '154023' time2 FROM dual
UNION ALL SELECT 'B', '20171103', '063027', '161010' FROM dual
UNION ALL SELECT 'C', '20171103', '065901', '160520' FROM dual
UNION ALL SELECT 'D', '20171103', '064632', '154923' FROM dual
UNION ALL SELECT 'B', '20171104', '063027', '161010' FROM dual
UNION ALL SELECT 'C', '20171104', '065901', '160520' FROM dual
UNION ALL SELECT 'C', '20171105', '065901', '160520' FROM dual
)
SELECT id
, FLOOR(s / (24*60*60)) || ' ' ||
TO_CHAR(TO_DATE(MOD(s, 24*60*60), 'sssss'), 'hh24:mi:ss') tm
FROM (SELECT id
, SUM(ROUND((TO_DATE(time2, 'hh24miss')- TO_DATE(time1, 'hh24miss'))*24*60*60)) s
FROM attend
WHERE edate BETWEEN '20171101' AND '20171130'
GROUP BY id
)
;
by rotic
[2017.11.07 14:13:58]
감사합니다.
더욱 더 공부해서 도움되는 사람이 되겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.