Monitoring Massa Ikatan Dinas
select 
   substr(kd_kantor,1,4) kd_kantor,count(1) total,
   sum(penempatan) penempatan, sum(tubel) tubel 
from spg_data_current a
   left join (select a.nip,sum(decode(status,'P',1,0)) penempatan,
   sum(decode(status,'TB',1,0)) tubel 
from spg_massa_ikatan_dinas a group by nip) b 
on a.nip=b.nip
where a.sts_pensiun='0'  and a.kd_kantor<>'1000000002'
group by substr(kd_kantor,1,4) order by kd_kantor