데이터베이스 질문과 답변
보통 근무시간은 근무종료시간-근무시작시간으로 구할수 있지만,
앞으로 계속 점심시간과 저녁시간을 뺀 시간함수를 구하고 싶습니다. (시간이 아닌.. 분으로)
예) 점심시간: AM11:30~12:00(30분)
저녁시간: PM17:00~17:30(30분) 이며...
근무시작시간이 20110321 AM08:30
근무종료시간이 20110321 PM18:00 이라고 한다면...
총 근무시간은 점심,저녁시간을 뺀 540분이 됩니다...
초보자이다보니 너무 막막하네요~ 도와주세요~~ ㅡ,.ㅡ
by 허재영
[2011.03.22 15:55:37]
아무리 계산해봐도 510분이네요
밥먹다 출퇴근하는 경우는 고려 안하고 만들어 봤네요.
with a as
(
select PERSON,
to_number(substr(A_TIME,1,2))*60 + to_number(substr(A_TIME,4,2)) A_TIME,
to_number(substr(P_TIME,1,2))*60 + to_number(substr(P_TIME,4,2)) P_TIME from
(
select 'P1' PERSON,'08:30' A_TIME, '18:00' P_TIME from dual
)
), b as
(
select
to_number(substr('11:30',1,2))*60 + to_number(substr('11:30',4,2)) RUN_ST_TIME,
to_number(substr('12:00',1,2))*60 + to_number(substr('12:00',4,2)) RUN_ED_TIME,
to_number(substr('17:00',1,2))*60 + to_number(substr('17:00',4,2)) NIG_ST_TIME,
to_number(substr('17:30',1,2))*60 + to_number(substr('17:30',4,2)) NIG_ED_TIME
from
(
select
'11:30' RUN_ST_TIME, '12:00' RUN_ED_TIME,
'17:00' NIG_ST_TIME, '17:30' NIG_ED_TIME
from dual
)
)
select PERSON,P_TIME - A_TIME - RUN_TIME - NIG_TIME WORK_TIME from
(
select PERSON,A_TIME,P_TIME,
case when A_TIME <= RUN_ST_TIME and P_TIME >= RUN_ED_TIME
then RUN_ED_TIME - RUN_ST_TIME end RUN_TIME,
case when A_TIME <= NIG_ST_TIME and P_TIME >= NIG_ED_TIME
then NIG_ED_TIME - NIG_ST_TIME end NIG_TIME
from a, b
)
by 문두
[2011.03.23 00:09:09]
WITH T AS
(
SELECT '08:30' AS START_TIME, '18:00' AS END_TIME FROM DUAL
)
SELECT ( TO_DATE(END_TIME,'HH24:MI') - TO_DATE(START_TIME,'HH24:MI') -
CASE WHEN '17:30' < END_TIME THEN TO_DATE('01:00','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '17:00' < END_TIME THEN TO_DATE(END_TIME,'HH24:MI') - TO_DATE('17:00','HH24:MI') + TO_DATE('00:30','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '12:00' < END_TIME THEN TO_DATE('00:30','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '11:30' < END_TIME THEN TO_DATE(END_TIME,'HH24:MI') - TO_DATE('11:30','HH24:MI')
END ) * 24 * 60 AS MI
FROM T
(
SELECT '08:30' AS START_TIME, '18:00' AS END_TIME FROM DUAL
)
SELECT ( TO_DATE(END_TIME,'HH24:MI') - TO_DATE(START_TIME,'HH24:MI') -
CASE WHEN '17:30' < END_TIME THEN TO_DATE('01:00','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '17:00' < END_TIME THEN TO_DATE(END_TIME,'HH24:MI') - TO_DATE('17:00','HH24:MI') + TO_DATE('00:30','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '12:00' < END_TIME THEN TO_DATE('00:30','HH24:MI') - TO_DATE('00:00','HH24:MI')
WHEN '11:30' < END_TIME THEN TO_DATE(END_TIME,'HH24:MI') - TO_DATE('11:30','HH24:MI')
END ) * 24 * 60 AS MI
FROM T
) 버튼을 클릭하여 작성 하시면 됩니다.