~ Tiez Notes ~

select aa.thn_jual, aa.productcode, aa.productdesc, sum(aa.cusosarreal) as osarreal, sum(aa.jatuhtempo) as jatuhtempo, sum(aa.bln6) as bln6,
sum(aa.bln12) as bln12, sum(aa.bln18) as bln18, sum(aa.bln24) as bln24, sum(aa.bln30) as bln30, sum(aa.bln36) as bln36,
sum(aa.bln42) as bln42, sum(aa.bln48) as bln48
from
(select a.norek, a.nopin, a.realisasidate, datepart(year, a.realisasidate) thn_jual, a.agingday, a.productcode,
(select productdesc from genproduct where productcode = a.productcode) as productdesc, a.cusosarreal,
(case when a.agingday > 0 then a.cusosarreal else 0 end) jatuhtempo,
(case when a.agingday <= 0 then a.cusosarreal else 0 end) nonjatuhtempo,
dateadd(month, -1, dateadd(month, a.tenor, a.realisasidate)) enddate,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 0 and 6 then a.cusosarreal else 0 end) bln6,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 7 and 12 then a.cusosarreal else 0 end) bln12,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 13 and 18 then a.cusosarreal else 0 end) bln18,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 19 and 24 then a.cusosarreal else 0 end) bln24,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 25 and 30 then a.cusosarreal else 0 end) bln30,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 31 and 36 then a.cusosarreal else 0 end) bln36,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 37 and 42 then a.cusosarreal else 0 end) bln42,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 43 and 48 then a.cusosarreal else 0 end) bln48,
(case when agingday <=0 and (a.tenor – a.tenorpaid) > 48 then a.cusosarreal end) lbh48
from rptfinancore a with(nolock)
where a.closetype = ’0′
) aa
group by aa.thn_jual, aa.productcode, aa.productdesc
order by aa.thn_jual, aa.productcode