SELECT CASE WHEN A.LACTRID IS NULL THEN (CASE WHEN A.LACTRNM IS NULL THEN '999' ELSE '222' END)ELSE '111' END AS SSS , CASE A.OUTERLACTRYN WHEN 'Y' THEN A.LACTRNM||'(외부)' ELSE A.LACTRNM END AS LACTRNM , B.jobrespcd AS jobrespcd , SUM(CASE WHEN TRIM(A.POSCD) = 'DT1' THEN A.NUMCNT ELSE 0 END) NUM_PAT , SUM(CASE WHEN TRIM(A.POSCD) = 'DT1' THEN A.PATCNT ELSE 0 END) CNT_PAT , SUM(CASE WHEN TRIM(A.POSCD) = 'DT2' THEN A.NUMCNT ELSE 0 END) NUM_PRO , SUM(CASE WHEN TRIM(A.POSCD) = 'DT2' THEN A.PATCNT ELSE 0 END) CNT_PRO , SUM(CASE WHEN TRIM(A.POSCD) = 'DT3' THEN A.NUMCNT ELSE 0 END) NUM_TEA , SUM(CASE WHEN TRIM(A.POSCD) = 'DT3' THEN A.PATCNT ELSE 0 END) CNT_TEA , SUM(CASE WHEN TRIM(A.POSCD) = 'DT4' THEN A.NUMCNT ELSE 0 END) NUM_OTH , SUM(CASE WHEN TRIM(A.POSCD) = 'DT4' THEN A.PATCNT ELSE 0 END) CNT_OTH , SUM(A.NUMCNT) NUM_TOT , SUM(A.PATCNT) CNT_TOT FROM (SELECT DOAT.LACTRNM AS LACTRNM , DOAT.LACTRID AS LACTRID , DOAT.OUTERLACTRYN AS OUTERLACTRYN , TRIM(DOTG.DOCTRINETRGTMANPOSCD) AS POSCD , COUNT(DISTINCT DOAT.TRGTMANID) AS PATCNT , COUNT(*) AS NUMCNT FROM ast.ayamdotg dotg INNER JOIN ast.ayaddoat doat ON dotg.trgtmanid = doat.trgtmanid AND dotg.rgstdd = doat.rgstdd AND dotg.seqno = doat.seqno AND dotg.instcd = doat.instcd AND doat.instcd = '{?instcd}' AND doat.attdncdd BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31' WHERE dotg.DOCTRINECD = 'D1' AND dotg.INSTCD = '{?instcd}' AND dotg.deldd = '00000000' GROUP BY doat.LACTRNM,DOAT.LACTRID,DOAT.OUTERLACTRYN, trim(dotg.doctrinetrgtmanposcd) ) A 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=A.LACTRID ) GROUP BY B.jobrespcd , CASE WHEN A.LACTRID IS NULL THEN (CASE WHEN a.LACTRNM IS NULL THEN '999' ELSE '222' END)ELSE '111' END , CASE A.OUTERLACTRYN WHEN 'Y' THEN a.LACTRNM||'(외부)' ELSE a.LACTRNM END ORDER BY SSS