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 'C0015' THEN 1 ELSE 0 END) AS B1 , SUM(CASE TMP.CLSCD WHEN 'C0016' THEN 1 ELSE 0 END) AS B2 , SUM(CASE TMP.CLSCD WHEN 'C0024' THEN 1 ELSE 0 END) AS B3 , SUM(CASE TMP.CLSCD WHEN 'C0025' THEN 1 ELSE 0 END) AS B4 , SUM(CASE TMP.CLSCD WHEN 'C0005' THEN 1 ELSE 0 END) AS B5 , SUM(CASE TMP.CLSCD WHEN 'C0010' THEN 1 ELSE 0 END) AS B6 , SUM(CASE TMP.CLSCD WHEN 'C0011' THEN 1 ELSE 0 END) AS B7 , SUM(CASE TMP.CLSCD WHEN 'C0007' THEN 1 ELSE 0 END) AS B8 , SUM(CASE TMP.CLSCD WHEN 'C0008' THEN 1 ELSE 0 END) AS B9 , (SUM(CASE TMP.CLSCD WHEN 'C0015' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0016' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0024' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0025' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0005' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0010' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0011' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0007' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0008' THEN 1 ELSE 0 END) ) AS BTOTAL , SUM(CASE TMP.CLSCD WHEN 'C0012' THEN 1 ELSE 0 END) AS C1 , SUM(CASE TMP.CLSCD WHEN 'C0013' THEN 1 ELSE 0 END) AS C2 , SUM(CASE TMP.CLSCD WHEN 'C0014' THEN 1 ELSE 0 END) AS C3 , (SUM(CASE TMP.CLSCD WHEN 'C0012' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0013' THEN 1 ELSE 0 END) + SUM(CASE TMP.CLSCD WHEN 'C0014' THEN 1 ELSE 0 END) ) AS CTOTAL 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.SCRNCD IN('C1','C2','XX') AND CLAS.CLSFLAG='C' AND CLAS.CLSLEVL ='3' AND CLAS.SCRNDISPYN='Y' AND CLAS.INSTCD ='{?instcd}' AND CLAS.DELDD = '00000000' AND CLAS.CLSSUPCD !='C0003' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD UNION ALL SELECT CLAS.CLSCD AS CLSCD , CLAS.CLSNM AS CLSNM , CLAS.CLSSUPCD AS CLSSUPCD , CAST(CLSCD AS VARCHAR(300)) AS PATHCD , CLAS.CLSSUPCD AS ORDCD FROM AST.AYAMCLAS CLAS WHERE CLAS.SCRNCD ='C3' AND CLAS.INSTCD ='{?instcd}' AND CLAS.DELDD = '00000000' ) 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