select a.conslrid as conslrid , a.conslrnm as conslrnm , sum(acttims) as acttims , sum(acttims_pid) as acttims_pid , sum(pro_acttims) as pro_acttims , sum(pro_pid) as pro_pid , sum(tea_acttims) as tea_acttims , sum(tea_pid) as tea_pid , sum(oth_acttims) as oth_acttims , sum(oth_pid) as oth_pid , sum( acttims + pro_acttims + tea_acttims + oth_acttims) as tot_acttims , sum(acttims_pid) as tot_pid , jobrespcd from ( SELECT patm.CONSLRID , aa.conslrnm as conslrnm , sum(patm.acttims) as acttims , count(distinct patm.PID) as acttims_pid , 0 as pro_acttims , '' as pro_pid , 0 as tea_acttims , '' as tea_pid , 0 as oth_acttims , '' as oth_pid , aa.jobrespcd FROM ast.AYAHPATM patm 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.dutplcecd in( '4050400000','4050100000') 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 ) aa ON (aa.conslrid = patm.conslrid ) WHERE patm.ACTFLAG = '2' AND patm.deldd = '00000000' AND patm.ACTDD between '{?fromdd}'||'01' and '{?todd}'||'31' AND patm.instcd = '{?instcd}' GROUP BY aa.jobrespcd, patm.CONSLRID ,aa.conslrnm UNION all SELECT biza.CONSLRID , aa.conslrnm as conslrnm , 0 as acttims , 0 as acttims_pid , SUM(CASE WHEN trim(biza.clscd) = 'E1011' then biza.acttims ELSE 0 END) pro_acttims , '' as pro_pid , SUM(CASE WHEN trim(biza.clscd) = 'E1012' then biza.acttims ELSE 0 END) tea_acttims , '' as tea_pid , SUM(CASE WHEN trim(biza.clscd) = 'E1020' then biza.acttims ELSE 0 END) oth_acttims , '' as oth_pid , aa.jobrespcd FROM ast.AYAHBIZA biza 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.dutplcecd in( '4050400000','405010biza0000') 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 ) aa ON (aa.conslrid = biza.conslrid ) WHERE biza.FTHACTDD BETWEEN '{?fromdd}'||'01' and '{?todd}'||'31' AND biza.INSTCD = '{?instcd}' GROUP BY aa.jobrespcd, biza.CONSLRID ,aa.conslrnm ) a GROUP BY conslrid, conslrnm, jobrespcd ORDER BY jobrespcd