SELECT usrb.usernm as conslrnm ,B.jobrespcd AS jobrespcd ,SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'D' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as D ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C01' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as C01 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C02' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as C02 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C03' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as C03 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C04' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as C04 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B01' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as B01 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B02' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as B02 ,SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B03' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as B03 ,SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'A' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as A ,SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'Z' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) as Z ,(SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'D' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C01' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C02' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C03' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'C04' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B01' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B02' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHECNTSCD) = 'B03' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'A' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END) +SUM(CASE WHEN trim(yysc.SCHEFLAGCD) = 'Z' THEN TRUNC(yysc.TODD) - TRUNC(yysc.FROMDD)+1 ELSE 0 END))as total FROM ast.AYAHYYSC yysc 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=yysc.conslrid ) LEFT OUTER JOIN com.zsumusrb usrb ON yysc.conslrid = usrb.userid AND yysc.fromdd BETWEEN usrb.userfromdd AND usrb.usertodd WHERE yysc.conslrid = usrb.userid AND yysc.instcd = '{?instcd}' AND yysc.deldd = '00000000' AND ((( yysc.fromdd BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31') OR ( yysc.todd BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31')) OR (( '{?fromdd}'||'01' BETWEEN yysc.fromdd AND yysc.todd) OR ( '{?todd}'||'31' BETWEEN yysc.fromdd AND yysc.todd))) GROUP BY B.jobrespcd ,usrb.usernm ORDER BY B.jobrespcd ,usrb.usernm