데이터베이스 질문과 답변
by Oracle10g [2016.12.07 17:19:01]
안녕하십니까 여러분 ㅠㅠ 다들 열코딩하고 계신가요~~?
추운날씨에 다들 감기조심하십시요^^
오늘도 도저히 머리를 싸매고 고민해도 해결되지 않는 문제가 있어 질문드립니다 ㅠㅠ
----주당만근시간----
주당만근시간이란 ?
- 한 주당 4시간이 생성되며, 생성조건은 그 주 만근(월~금 5일 근무)을 했을 경우임.
1. 평일이면서 공휴일인 경우 근무편성 안하지만 주당만근시간 계산하는 근무일수에 포함
2. 매월 첫째날이 월요일(ER_CALENDAR.DAY ='2')이 아닌경우에는 이전월 마지막 월요일로부터 근무한
근무일수를 포함하여 근무일수 계산하여 주당만근시간 발생
3. 매월 마지막 근무일이 금요일(ER_CALENDAR.DAY ='6')이 아닌 경우에는 버림처리
EX) 11월 근무자료 집계시 20161128 월요일 ~ 20161202 금요일 주당만근시간은 5일만근 아니므로 버림
12월 근무자료 집계시 20161128 월요일 ~ 20161202 금요일이 되는 시점으로 5일만근 이므로
12월 집계시 첫째주 주당만근시간 생성
모든 근무자가 만근을 했다는 가정하에 11월, 12월 집계를 한다면
2016년 11월 집계시에는 우선 5째주 주당만근시간은 발생하지 않는다. (이유 : 3번 항목)
또 전달 10월 31일에 근무를 했다면 첫째~넷째주 4주가 생성되므로
4*4 주당만근시간 16시간생성
전달 10월 31일에 근무를 안했다면 첫째~넷째주 3주가 생성되므로(첫째주 제외)
4*3 주당만근시간 12시간생성
스크린샷 설명
ER_SHORTDUTY -- 일별 근무내역 (DUTEXCLYN <>'Y' 대직을 새운경우
원래 근무자는 (DUTEXCLYN가 Y로 변하고
대직으로 설정된 사람은 원래근무자의 근무데이터에 근무자만 바꿔서 로우한개 더 생성됨
따라서 DUTEXCLYN <>'Y' 데이터만 집계하면 됨)
ER_CALENDAR -- 일-1 월-2 화-3 수-4 목-5 금-6 토-7
설명은 여기까지이고 프로시져로 개발예정입니다.....어떤식으로 짜면 될까요...
결과값은 월별,사원별,주당만근시간값 입니다....
고수님들 도와주십시요 ㅠㅠㅠ
-- 테스트용 샘플 데이터. 시작. --
WITH er_calendar AS
(
SELECT TO_CHAR(dt, 'yyyymmdd') ymd
, TO_CHAR(dt, 'd') day
, CASE WHEN TO_CHAR(dt, 'd') IN ('1','7') THEN 'Y' ELSE 'N' END holadyn
FROM (SELECT TRUNC(sysdate, 'yy') + LEVEL - 1 dt
FROM dual
CONNECT BY LEVEL <= 365
)
)
, er_shortduty AS
(
SELECT '20161128' dutyymd, '7788' empno, 'N' dutexclyn FROM dual
UNION ALL SELECT '20161129', '7788', 'N' FROM dual
UNION ALL SELECT '20161130', '7788', 'N' FROM dual
UNION ALL SELECT '20161201', '7788', 'N' FROM dual
UNION ALL SELECT '20161202', '7788', 'N' FROM dual
UNION ALL SELECT '20161203', '7788', 'N' FROM dual
UNION ALL SELECT '20161204', '7788', 'N' FROM dual
UNION ALL SELECT '20161205', '7788', 'N' FROM dual
UNION ALL SELECT '20161206', '7788', 'N' FROM dual
UNION ALL SELECT '20161207', '7788', 'N' FROM dual
UNION ALL SELECT '20161208', '7788', 'N' FROM dual
UNION ALL SELECT '20161209', '7788', 'N' FROM dual
)
-- 테스트용 샘플 데이터. 끝. --
SELECT empno
, COUNT(*) * 4 AS tm -- 주당 4시간
FROM (SELECT c.empno
FROM (SELECT TO_CHAR(NEXT_DAY(ym - 1, 6) - 4 , 'yyyymmdd') sdt -- 기간-From
, TO_CHAR(NEXT_DAY(LAST_DAY(ym)-7, 6), 'yyyymmdd') edt -- 기간-To
FROM (SELECT TO_DATE('201612', 'yyyymm') ym FROM dual) -- 조회년월
) a
, er_calendar b
, er_shortduty c
WHERE b.ymd BETWEEN a.sdt AND a.edt
AND b.day BETWEEN '2' AND '6' -- 토일 제외
AND b.ymd = c.dutyymd
AND (c.dutexclyn = 'N' OR b.holadyn = 'Y') -- 근무 or 휴일
GROUP BY c.empno, TRUNC(TO_DATE(b.ymd, 'yyyymmdd'), 'd') -- 주별 집계
HAVING COUNT(*) = 5 -- 5일 만근
)
GROUP BY empno
;
마농님..맵번 도움주셔서 너무감사합니다.
설명도중 근무제외 'Y' 'N' 플래그값 잘못설명드린부분 죄송합니다....
항상 마농님 처럼 되기위해 열심히 배우고있습니다.
감사합니다 ㅎㅎ
) 버튼을 클릭하여 작성 하시면 됩니다.