안녕하세요.
매번 비회원으로 눈팅만하다가 쿼리작성중에 막혀버려서...도움좀 주세요 ㅠㅠ
일단 만들려는게 시간대별 근무자 현황표 입니다.
대충 쿼리를 만들기는 했는데..더이상 진행이 안되어 도움요청 드립니다.
조건:
(출근일자) : (시작년월일) 20120901 ~ 20120919 (종료년월일), 또는 201209(년월)
구현조건 : 사용자 출퇴근 정보 테이블의 테이타를 가공하여 아래 조회결과와 같이 일자별로 각 시간별 근무자수를 통계냅니다.
예) a근무자가 20120901090510 (2012년09월01일 09시05분10초)에 출근하여 20120901180510 에 퇴근하였다면 각 출근부터 퇴근사이의 매 시간에 카운트 1씩 줘서
시간대별 근무하고 있는 사용자수를 통계내는 것입니다.
만일 09시 부터 18시까지 5명의 근무자가 있다면 매시각 근무자수는 5 가 나타나야 합니다.
아래 조회결과는 실제 완성되었을때의 모습입니다.
(주의) 동일한 근무자가 동일한 년월일에서 출퇴근 시간만 다르고 여러건이 있을경우 시작일자(최소시간), 퇴근시간(마지막시간) 을 참고하여 근무시간을 구하면 됩니다.
해당일에 출근일자는 있고 퇴근시간이 없는 근무자는 구하지 않아도 됩니다.
조회결과:
구 분 |
0시 |
1시 |
2시 |
3시 |
4시 |
5시 |
6시 |
7시 |
8시 |
9시 |
10시 |
11시 |
12시 |
13시 |
14시 |
15시 |
16시 |
17시 |
18시 |
19시 |
20시 |
21시 |
22시 |
23시 |
09월13일 |
5 |
7 |
22 |
15 |
15 |
15 |
15 |
15 |
14 |
11 |
10 |
10 |
10 |
10 |
10 |
10 |
10 |
7 |
7 |
3 |
3 |
3 |
3 |
0 |
09월12일 |
1 |
2 |
2 |
2 |
2 |
2 |
4 |
5 |
8 |
10 |
10 |
10 |
11 |
13 |
13 |
13 |
11 |
11 |
9 |
6 |
6 |
6 |
5 |
3 |
09월11일 |
0 |
0 |
0 |
0 |
1 |
1 |
2 |
5 |
8 |
8 |
7 |
7 |
10 |
10 |
10 |
10 |
10 |
6 |
6 |
3 |
3 |
3 |
1 |
0 |
09월10일 |
1 |
1 |
1 |
1 |
1 |
0 |
2 |
3 |
6 |
7 |
7 |
7 |
10 |
11 |
11 |
11 |
10 |
7 |
7 |
3 |
3 |
3 |
2 |
0 |
09월09일 |
1 |
1 |
1 |
1 |
1 |
1 |
4 |
4 |
5 |
7 |
6 |
6 |
8 |
10 |
10 |
10 |
9 |
7 |
6 |
4 |
4 |
4 |
4 |
0 |
테이블 : 녹색컬럼을 이용하시면 되고 나머지 컬럼들은 무시하셔도 됩니다.
USER_UID |
CHAR(5) |
사용자UID |
GO_DATETIME |
CHAR(14) |
출근시간 |
LATE_YN |
VARCHAR2(2) |
지각여부 |
LEAV_DATETIME |
CHAR(14) |
퇴근시간 |
HOLIDAY_FROM |
CHAR(8) |
휴가일자FROM |
HOLIDAY_TO |
CHAR(8) |
휴가일자TO |
HOLIDAY_TYPE |
VARCHAR2(6) |
휴가구분 |
REPLACE_DAY |
CHAR(8) |
대체일자 |
HOLIDAY_REASON |
VARCHAR2(500) |
휴가사유 |
CREATED_USER_UID |
CHAR(5) |
생성자UID |
CREATED_DATETIME |
CHAR(14) |
생성일시 |
실제데이타:
USER_UID |
GO_DATETIME |
|
LEAV_DATETIME |
90021 |
20120901055834 |
0 |
20120901150206 |
90020 |
20120901055942 |
0 |
20120901101747 |
90019 |
20120901060222 |
0 |
20120901065643 |
90019 |
20120901065647 |
0 |
20120901123742 |
90002 |
20120901071248 |
0 |
|
90001 |
20120901071635 |
0 |
|
90006 |
20120901083815 |
0 |
20120901094510 |
99996 |
20120901085642 |
0 |
|
90007 |
20120901085723 |
0 |
20120901180125 |
90001 |
20120901085914 |
0 |
|
90009 |
20120901085925 |
0 |
20120901180227 |
90001 |
20120901090104 |
1 |
20120901090124 |
90008 |
20120901090126 |
1 |
20120901172326 |
90001 |
20120901090128 |
1 |
20120901090150 |
90001 |
20120901090253 |
1 |
20120901092249 |
90001 |
20120901092253 |
1 |
|
90006 |
20120901094515 |
1 |
20120901162142 |
90022 |
20120901101754 |
1 |
20120901101802 |
90018 |
20120901101811 |
1 |
20120901101823 |
90020 |
20120901101827 |
1 |
20120901150040 |
90002 |
20120901123140 |
1 |
20120901123155 |
90002 |
20120901123158 |
1 |
20120901125013 |
90019 |
20120901123752 |
1 |
20120901123906 |
90019 |
20120901123914 |
1 |
20120901123935 |
90019 |
20120901123944 |
1 |
|
90002 |
20120901125359 |
1 |
20120901125440 |
90019 |
20120901125441 |
1 |
|
90013 |
20120901125750 |
1 |
20120901220034 |
90011 |
20120901125830 |
1 |
|
90002 |
20120901130024 |
1 |
20120901130042 |
90010 |
20120901130036 |
1 |
20120901220013 |
90011 |
20120901130048 |
1 |
20120901154439 |
90002 |
20120901130055 |
1 |
20120901140135 |
90019 |
20120901130118 |
1 |
20120901150029 |
90012 |
20120901130204 |
1 |
20120901220005 |
90001 |
20120901130839 |
1 |
20120901140630 |
90018 |
20120901134711 |
1 |
|
90002 |
20120901140527 |
1 |
20120901152652 |
90018 |
20120901140617 |
1 |
20120901150110 |
90001 |
20120901150225 |
1 |
20120901154518 |
90006 |
20120901162151 |
1 |
20120901180219 |
90011 |
20120901163615 |
1 |
20120901185102 |
90008 |
20120901172330 |
1 |
20120901175946 |
90014 |
20120901175740 |
1 |
20120901190413 |
90015 |
20120901175904 |
1 |
20120902020924 |
90016 |
20120901175945 |
1 |
|
90017 |
20120901175953 |
1 |
20120901181748 |
90017 |
20120901181753 |
1 |
20120902090008 |
90011 |
20120901185107 |
1 |
20120901220003 |
90014 |
20120901190424 |
1 |
20120902020248 |
99999 |
20120901225016 |
1 |
20120901225047 |
90014 |
20120902020252 |
0 |
20120902044904 |
90015 |
20120902020928 |
0 |
20120902090014 |
90014 |
20120902045059 |
0 |
20120902090024 |
90021 |
20120902065825 |
0 |
20120902160024 |
90020 |
20120902070023 |
0 |
20120902160009 |
90019 |
20120902070107 |
0 |
20120902135133 |
90018 |
20120902070335 |
0 |
|
90002 |
20120902071414 |
0 |
20120902071507 |
90018 |
20120902071525 |
0 |
20120902160048 |
90006 |
20120902085844 |
0 |
20120902092631 |
90012 |
20120902090020 |
1 |
20120902180106 |
90002 |
20120902090025 |
1 |
20120902090048 |
90009 |
20120902090037 |
1 |
20120902180124 |
90016 |
20120902090055 |
1 |
20120902090101 |
90007 |
20120902090134 |
1 |
20120902180020 |
90006 |
20120902092635 |
1 |
20120902100352 |
90006 |
20120902100356 |
1 |
20120902102335 |
90006 |
20120902102338 |
1 |
20120902102845 |
90006 |
20120902102849 |
1 |
20120902103514 |
90006 |
20120902103518 |
1 |
20120902105405 |
90006 |
20120902105408 |
1 |
20120902180124 |
90011 |
20120902125202 |
1 |
20120902133942 |
90013 |
20120902125922 |
1 |
20120902220041 |
90010 |
20120902130009 |
1 |
20120902164322 |
90008 |
20120902130241 |
1 |
20120902220026 |
90011 |
20120902134117 |
1 |
20120902141614 |
90019 |
20120902135139 |
1 |
20120902160023 |
90011 |
20120902141624 |
1 |
20120902220003 |
99999 |
20120902161849 |
1 |
20120902162003 |
90010 |
20120902164325 |
1 |
20120902164330 |
90010 |
20120902164336 |
1 |
20120902164353 |
90010 |
20120902164356 |
1 |
20120902164406 |
90002 |
20120902164412 |
1 |
20120902164416 |
90010 |
20120902164424 |
1 |
20120902164432 |
90010 |
20120902164438 |
1 |
20120902164443 |
90010 |
20120902164455 |
1 |
20120902164503 |
90010 |
20120902164604 |
1 |
20120902164609 |
90010 |
20120902164614 |
1 |
20120902220049 |
90016 |
20120902175747 |
1 |
20120903003619 |
90014 |
20120902175830 |
1 |
20120903090018 |
90017 |
20120902180011 |
1 |
20120903090040 |
90015 |
20120902180015 |
1 |
20120902214101 |
99999 |
20120902193038 |
1 |
20120902193251 |
90015 |
20120902214104 |
1 |
20120902214214 |
90015 |
20120902214219 |
1 |
20120902214518 |
90015 |
20120902214521 |
1 |
20120903090006 |
90016 |
20120903003718 |
0 |
20120903090017 |
90003 |
20120903065744 |
0 |
20120903160457 |
90021 |
20120903065929 |
0 |
20120903160033 |
90004 |
20120903065955 |
0 |
20120903160053 |
90022 |
20120903085857 |
0 |
20120903180125 |
90007 |
20120903085919 |
0 |
20120903093429 |
90024 |
20120903085943 |
0 |
20120903180115 |
90023 |
20120903090920 |
1 |
20120903180023 |
99998 |
20120903092251 |
1 |
20120903093846 |
99999 |
20120903092641 |
1 |
|
90025 |
20120903093445 |
1 |
20120903180118 |
90001 |
20120903093816 |
1 |
20120903155419 |
99998 |
20120903093941 |
1 |
20120903094446 |
90019 |
20120903125619 |
1 |
20120903204131 |
90005 |
20120903130038 |
1 |
20120903220106 |
90020 |
20120903130107 |
1 |
|
90018 |
20120903130208 |
1 |
20120903155627 |
90002 |
20120903132757 |
1 |
|
99998 |
20120903141524 |
1 |
20120903142058 |
77777 |
20120903143318 |
1 |
20120903144446 |
90020 |
20120903144400 |
1 |
|
77777 |
20120903144453 |
1 |
20120903145302 |
99999 |
20120903145336 |
1 |
20120903193808 |
99998 |
20120903145434 |
1 |
|
90020 |
20120903150253 |
1 |
20120903190649 |
99998 |
20120903151140 |
1 |
|
99998 |
20120903151657 |
1 |
20120903154558 |
77777 |
20120903154603 |
1 |
|
90018 |
20120903155631 |
1 |
20120903215955 |
90001 |
20120903165906 |
1 |
20120903165925 |
90002 |
20120903170321 |
1 |
20120903184155 |
90001 |
20120903170609 |
1 |
20120903183452 |
90007 |
20120903175817 |
1 |
20120904090012 |
90006 |
20120903175830 |
1 |
20120904044045 |
90008 |
20120903175916 |
1 |
20120904090043 |
77777 |
20120903185343 |
1 |
|
99998 |
20120903190253 |
1 |
20120903190625 |
90020 |
20120903190658 |
1 |
20120903200220 |
90020 |
20120903200222 |
1 |
20120903220007 |
90019 |
20120903204140 |
1 |
20120903220051 |
99998 |
20120903210301 |
1 |
20120903210445 |
99998 |
20120903234458 |
1 |
20120903234957 |
제가만든쿼리...
하루짜리 쿼리는 만들었는데...한달짜리만들려니 답이 안나와요 ㅠㅠ 부탁드립니다..
SELECT (SUBSTR(MAX(A.YMD),5,2)||'월'||SUBSTR(MAX(A.YMD),7,2)||'일') AS a1,
SUM(A.H0) AS a2,
SUM(A.H1) AS a3,
SUM(A.H2) AS a4,
SUM(A.H3) AS a5,
SUM(A.H4) AS a6,
SUM(A.H5) AS a7,
SUM(A.H6) AS a8,
SUM(A.H7) AS a9,
SUM(A.H8) AS a10,
SUM(A.H9) AS a11,
SUM(A.H10) AS a12,
SUM(A.H11) AS a13,
SUM(A.H12) AS a14,
SUM(A.H13) AS a15,
SUM(A.H14) AS a16,
SUM(A.H15) AS a17,
SUM(A.H16) AS a18,
SUM(A.H17) AS a19,
SUM(A.H18) AS a20,
SUM(A.H19) AS a21,
SUM(A.H20) AS a22,
SUM(A.H21) AS a23,
SUM(A.H22) AS a24,
SUM(A.H23) AS a25
FROM
(SELECT SUBSTR(A.CREATED_DATETIME,1,8) AS YMD,
A.USER_UID,
MIN(A.GO_DATETIME),
MAX(A.LEAV_DATETIME),
MAX(A.CREATED_DATETIME),
SUBSTR(MIN(A.GO_DATETIME),9,2) AS STIME,
NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) AS ETIME,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 0 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 0 THEN 1 ELSE 0 END) AS H0,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 1 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 1 THEN 1 ELSE 0 END) AS H1,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 2 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 2 THEN 1 ELSE 0 END) AS H2,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 3 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 3 THEN 1 ELSE 0 END) AS H3,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 4 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 4 THEN 1 ELSE 0 END) AS H4,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 5 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 5 THEN 1 ELSE 0 END) AS H5,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 6 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 6 THEN 1 ELSE 0 END) AS H6,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 7 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 7 THEN 1 ELSE 0 END) AS H7,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 8 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 8 THEN 1 ELSE 0 END) AS H8,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 9 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 9 THEN 1 ELSE 0 END) AS H9,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 10 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 10 THEN 1 ELSE 0 END) AS H10,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 11 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 11 THEN 1 ELSE 0 END) AS H11,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 12 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 12 THEN 1 ELSE 0 END) AS H12,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 13 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 13 THEN 1 ELSE 0 END) AS H13,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 14 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 14 THEN 1 ELSE 0 END) AS H14,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 15 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 15 THEN 1 ELSE 0 END) AS H15,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 16 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 16 THEN 1 ELSE 0 END) AS H16,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 17 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 17 THEN 1 ELSE 0 END) AS H17,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 18 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 18 THEN 1 ELSE 0 END) AS H18,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 19 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 19 THEN 1 ELSE 0 END) AS H19,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 20 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 20 THEN 1 ELSE 0 END) AS H20,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 21 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 21 THEN 1 ELSE 0 END) AS H21,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 22 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 22 THEN 1 ELSE 0 END) AS H22,
(CASE WHEN SUBSTR(MIN(A.GO_DATETIME),9,2) <= 23 AND NVL(SUBSTR(MAX(A.LEAV_DATETIME),9,2),23) >= 23 THEN 1 ELSE 0 END) AS H23
FROM DILIGENCE A
WHERE SUBSTR(A.CREATED_DATETIME,1,8) = '20120903'
AND A.USER_UID BETWEEN '90003' AND '90025'
GROUP BY SUBSTR(A.CREATED_DATETIME,1,8),A.USER_UID
|