SELECT CASE WHEN EFDM.PASTORID IS NULL THEN (CASE WHEN EFDM.PASTORNM IS NULL THEN '999' ELSE '222' END)ELSE '111' END AS SSS , CASE EFDM.OUTERPASTORYN WHEN 'Y' THEN EFDM.PASTORNM||'(외부)' ELSE EFDM.PASTORNM END AS PASTORNM , B.jobrespcd AS jobrespcd , SUM(CASE TMP.CLSCD WHEN 'C0017' THEN 1 ELSE 0 END) AS A1 , SUM(CASE TMP.CLSCD WHEN 'C0018' THEN 1 ELSE 0 END) AS A2 , SUM(CASE TMP.CLSCD WHEN 'C0019' THEN 1 ELSE 0 END) AS A3 , SUM(CASE TMP.CLSCD WHEN 'C0022' THEN 1 ELSE 0 END) AS A4 , SUM(CASE TMP.CLSCD WHEN 'C0023' THEN 1 ELSE 0 END) AS A5 , SUM(CASE TMP.CLSCD WHEN 'C0021' THEN 1 ELSE 0 END) AS A6 , ( SUM(CASE TMP.CLSCD WHEN 'C0017' THEN 1 ELSE 0 END) +SUM(CASE TMP.CLSCD WHEN 'C0018' THEN 1 ELSE 0 END) +SUM(CASE TMP.CLSCD WHEN 'C0019' THEN 1 ELSE 0 END) +SUM(CASE TMP.CLSCD WHEN 'C0022' THEN 1 ELSE 0 END) +SUM(CASE TMP.CLSCD WHEN 'C0023' THEN 1 ELSE 0 END) +SUM(CASE TMP.CLSCD WHEN 'C0021' THEN 1 ELSE 0 END)) AS ATOTAL FROM AST.AYAHEFDM EFDM LEFT OUTER JOIN ( SELECT usrd.userid AS conslrid , usrb.usernm AS conslrnm , usrb.mpphonno AS mpphontel , empl.hosinseqno AS depttel , empl.jobrespcd AS jobrespcd FROM com.zsumusrb usrb INNER JOIN com.zsumusrd usrd ON usrd.userid = usrb.userid AND TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN usrd.fromdd AND usrd.todd AND usrd.dutplceinstcd = '{?instcd}' AND usrd.psnworkyn = 'N' AND usrd.userid <> 'STS' LEFT OUTER JOIN mis.RPBVEMPL empl ON usrb.userid = empl.emplno WHERE TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN usrd.fromdd AND usrd.todd AND TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN USRB.USERFROMDD AND USRB.USERTODD AND usrd.dutplcecd IN('4050400000','4050100000') ORDER BY jobrespcd, USRB.USERNM )B ON (B.conslrid=EFDM.PASTORID ) INNER JOIN ( SELECT CLAS.CLSCD AS CLSCD , CLAS.CLSNM AS CLSNM , CLAS.CLSSUPCD AS CLSSUPCD , DECODE(CLAS.CLSLEVL,'3',TO_CHAR(CLSCD),TO_CHAR(CLSSUPCD)||'.'||TO_CHAR(CLSCD)) AS PATHCD , CLAS.CLSSUPCD AS ORDCD FROM AST.AYAMCLAS CLAS START WITH CLAS.CLSSUPCD='C0003' AND CLAS.CLSFLAG='C' AND CLAS.CLSLEVL ='3' AND CLAS.SCRNDISPYN='Y' AND CLAS.INSTCD ='{?instcd}' AND CLAS.DELDD = '00000000' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD ) TMP ON(TMP.CLSCD = EFDM.CLSCD ) WHERE EFDM.INSTCD='{?instcd}' AND EFDM.CLSFLAG='C' AND EFDM.DELDD='00000000' AND EFDM.FROMDD BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31' GROUP BY B.jobrespcd , CASE WHEN EFDM.PASTORID IS NULL THEN (CASE WHEN EFDM.PASTORNM IS NULL THEN '999' ELSE '222' END)ELSE '111' END , CASE EFDM.OUTERPASTORYN WHEN 'Y' THEN EFDM.PASTORNM||'(외부)' ELSE EFDM.PASTORNM END ORDER BY SSS