select
a.bill_No,a.unique_id_no,a.exchange_code,a.phone_no,b.collection_tml_code,
a.bill_date,b.receipt_date,months_between(b.receipt_date,a.bill_date)/12
age,
CASE WHEN months_between(b.receipt_date,a.bill_date)/12 <1 THEN 0
WHEN months_between(b.receipt_date,a.bill_date)/12 <2 THEN 3
WHEN
months_between(b.receipt_date,a.bill_date)/12 <4 THEN 4
WHEN months_between(b.receipt_date,a.bill_date)/12 <6 THEN 8
WHEN months_between(b.receipt_date,a.bill_date)/12 >6 THEN 10 END AS "RATE",
CASE WHEN months_between(b.receipt_date,a.bill_date)/12 <1 THEN 0
WHEN months_between(b.receipt_date,a.bill_date)/12 <2 THEN
3*b.receipt_amount/100
WHEN
months_between(b.receipt_date,a.bill_date)/12 <4 THEN
4*b.receipt_amount/100
WHEN months_between(b.receipt_date,a.bill_date)/12 <6 THEN
8*b.receipt_amount/100
WHEN months_between(b.receipt_date,a.bill_date)/12 >6 THEN
10*b.receipt_amount/100 END AS
"incentive",
b.receipt_amount from bill_master a,receipt_master
b,closed_working_lines c
where a.unique_id_no=b.unique_id_no and b.unique_id_no=c.unique_id_no
and
a.bill_no=b.bill_no and b.receipt_date between '01-feb=08' and '31-mar-08' and c.billed='F'
and a.class_code not like 'CG%' and b.payment_realised in('P','Y')
order by unique_id_no,receipt_date
/