SELECT CASE A.CLS WHEN '9' THEN 'Z9' ELSE 'A0' END AS CLS , CASE A.CLS WHEN '9' THEN '월별총인원' ELSE C.conslrnm END AS conslrnm , CASE A.CLS WHEN '9' THEN '9999' ELSE NVL(C.jobrespcd,'9990') END AS jobrespcd , SUM(C.A1) AS A1 , SUM(C.A2) AS A2 , SUM(C.A3) AS A3 , SUM(C.A4) AS A4 , SUM(C.A5) AS A5 , SUM(C.A7) AS A7 , SUM(C.A6) AS A6 , SUM(C.B2) AS B2 , SUM(C.B1) AS B1 , SUM(C.TOTAL) AS TOTAL FROM ( SELECT '9' AS CLS FROM DUAL UNION ALL SELECT '1' AS CLS FROM DUAL )A ,( SELECT TRIM(B.conslrnm) AS conslrnm , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A1' THEN 1 ELSE 0 END) AS A1 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A2' THEN 1 ELSE 0 END) AS A2 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A3' THEN 1 ELSE 0 END) AS A3 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A4' THEN 1 ELSE 0 END) AS A4 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A5' THEN 1 ELSE 0 END) AS A5 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A7' THEN 1 ELSE 0 END) AS A7 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A6' THEN 1 ELSE 0 END) AS A6 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'B2' THEN 1 ELSE 0 END) AS B2 , SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'B1' THEN 1 ELSE 0 END) AS B1 , (SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A1' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A2' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A3' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A4' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A5' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A7' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'A6' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'B2' THEN 1 ELSE 0 END) +SUM(CASE CFAT.CONFDETLFLAGCD WHEN 'B1' THEN 1 ELSE 0 END)) AS TOTAL , CFAT.ATTNTRID AS ATTNTRID , B.jobrespcd AS jobrespcd FROM AST.AYADCFAT CFAT ,(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.dutplcecd = '4050400000' 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 usrb.usertodd='99991231' UNION ALL 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.dutplcecd = '4050100000' AND usrd.psnworkyn = 'N' LEFT OUTER JOIN mis.RPBVEMPL empl ON usrb.userid = empl.emplno WHERE TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN usrb.userfromdd AND usrb.usertodd AND usrb.usertodd='99991231' ORDER BY jobrespcd ,conslrnm )B WHERE CFAT.INSTCD = '{?instcd}' AND B.conslrid = CFAT.ATTNTRID AND CFAT.CONFDD BETWEEN '{?fromdd}' ||'01' AND '{?todd}'||'31' GROUP BY B.jobrespcd, CFAT.ATTNTRID ,B.conslrnm )C GROUP BY CASE A.CLS WHEN '9' THEN 'Z9' ELSE 'A0' END , CASE A.CLS WHEN '9' THEN '월별총인원' ELSE C.conslrnm END , CASE A.CLS WHEN '9' THEN '9999' ELSE NVL(C.jobrespcd,'9990') END ORDER BY jobrespcd, conslrnm