set verify off
col day_1 new_value day_1
col day_5 new_value day_5
col day_10 new_value day_10
col day_15 new_value day_15
col day_19 new_value day_19
select to_Char(to_date('1/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_1 from dual;
select to_Char(to_date('5/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_5 from dual;
select to_Char(to_date('10/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_10 from dual;
select to_Char(to_date('15/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_15 from dual;
select to_Char(to_date('19/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_19 from dual;
with t as
(
select 1 as invoice,5000 as tot_amount,'1/1/2008' as pay_date,100 as payment from dual
union
select 1 ,5000 ,'5/1/2008' ,200 from dual
union
select 2 ,5000 ,'10/1/2008' ,400 from dual
union
select 2 ,5000 ,'15/1/2008' ,500 from dual
union
select 3 ,5000 ,'19/1/2008' ,600 from dual
)
select
invoice,tot_amount,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),1,payment)) "&day_1",
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),5,payment)) "&day_5",
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),10,payment)) "&day_10",
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),15,payment)) "&day_15",
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),19,payment)) "&day_19"
from t
group by
invoice,tot_amount
order by 1;
Code:
INVOICE TOT_AMOUNT 01/2008 01/2008 01/2008 01/2008 01/2008
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 5000 100 200
2 5000 400 500
3 5000 600