SELECT voc_a.deptnm, voc_a.deptcd, substr(voc_a.replreqdt,1,8) as replreqdd, (SELECT cdnm FROM com.zbcmcode WHERE cdgrupid = 'C0001' and cdid = voc_b.acpttypecd) as acpttype, (SELECT cdnm FROM com.zbcmcode WHERE cdgrupid = 'C0003' and cdid = voc_b.acptkindlrgcd) as acptkindlrg, voc_b.acpttitl, substr(voc_a.replacptdt,1,8) as replacptdd, substr(voc_a.repldt,1,8) as repldd, voc_a.acptdd, voc_a.acptseq, voc_a.rgstseq, (select count(*) from crm.cuvhrqrp where (replreqdt is not null or replreqdt <> '') and acptdd >= '{?srchacptdd1}' and acptdd <= '{?srchacptdd2}' and deptcd = '{?deptcd}' and instcd = '{?instcd}') as allcount, (select count(*) from crm.cuvhrqrp where (replreqdt is not null or replreqdt <> '') and (repldt is null or repldt = '') and acptdd >= '{?srchacptdd1}' and acptdd <= '{?srchacptdd2}' and deptcd = '{?deptcd}' and instcd = '{?instcd}') as notcount, (to_date(to_char(sysdate, 'YYYYMMDD')) - to_date(substr(voc_a.replreqdt,1,8))) as nodays FROM crm.cuvhrqrp voc_a, crm.cuvhacpi voc_b WHERE voc_a.acptdd = voc_b.acptdd and voc_a.acptseq = voc_b.acptseq and (voc_a.replreqdt is not null or voc_a.replreqdt <> '') and voc_b.acptdd >= '{?srchacptdd1}' and voc_b.acptdd <= '{?srchacptdd2}' and voc_a.deptcd = '{?deptcd}' and voc_b.instcd = '{?instcd}' ORDER BY voc_a.deptcd, voc_a.acptdd desc