union 때문인지 쿼리 툴에서 돌렸을때는 30초정도고
업무중에 돌리면 2분정도 나오네요;;;
이것저것 case 문도 써보고 inline view로 안쪽에서 데이터 뽑아서 해볼려고 하고 있는데
몇일동안 해보고 있는데 잘안되네요 ㅠㅠ
뭔가 다른 방법이 없을까요?
부탁드립니다~~
0 로그인 10
0 준비중 11
0 통화중 12
0 작업 13
0 대기 14
0 휴식 15
0 교육 16
0 회의 17
0 식사 18
0 면담 19
0 업무 20
0 지원 21
0 기타 22
SELECT A.cnt AS cnt
,A.cnt_name AS cnt_name
,A.ord AS ord
FROM
(
SELECT count(A.seqno) AS cnt
,'로그인' AS cnt_name
,'10' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE <> '1' and A.AGENT_STATE <> '9'
UNION ALL
SELECT count(A.seqno) AS cnt
,'준비중' AS cnt_name
,'11' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND (A.ir_code = '0')
UNION ALL
SELECT count(A.seqno) AS cnt
,'통화중' AS cnt_name
,'12' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '4'
UNION ALL
SELECT count(A.seqno) AS cnt
,'작업' AS cnt_name
,'13' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.agent_state = '2' and A.agent_state = '5' and A.agent_state = '6'
AND A.ir_code = '211'
UNION ALL
SELECT count(A.seqno) AS cnt
,'대기' AS cnt_name
,'14' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND (A.ir_code = '209' OR A.ir_code = '210')
UNION ALL
SELECT count(A.seqno) AS cnt
,'휴식' AS cnt_name
,'15' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '201'
UNION ALL
SELECT count(A.seqno) AS cnt
,'교육' AS cnt_name
,'16' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '202'
UNION ALL
SELECT count(A.seqno) AS cnt
,'회의' AS cnt_name
,'17' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '203'
UNION ALL
SELECT count(A.seqno) AS cnt
,'식사' AS cnt_name
,'18' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '204'
UNION ALL
SELECT count(A.seqno) AS cnt
,'면담' AS cnt_name
,'19' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '205'
UNION ALL
SELECT count(A.seqno) AS cnt
,'업무' AS cnt_name
,'20' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B, TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '206'
UNION ALL
SELECT count(A.seqno) AS cnt
,'지원' AS cnt_name
,'21' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B
,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '207'
UNION ALL
SELECT count(A.seqno) AS cnt
,'기타' AS cnt_name
,'22' AS ord
FROM TBL_AGENT_STATUS A
,(SELECT empno, MAX(seqno) seqno
FROM TBL_AGENT_STATUS
GROUP BY empno
) B ,TBL_AGENT C
WHERE A.seqno = B.seqno
AND A.empno = C.empno
AND A.reg_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND C.duty_cd = '10'
AND A.AGENT_STATE = '2'
AND A.ir_code = '208'
) A ORDER BY A.ord |