[EMAIL PROTECTED]> create table cover (
2 id integer,
3 cover varchar2(1),
4 premium number(5)
5 );
Table created.
[EMAIL PROTECTED]> create table bayar (
2 id integer,
3 amt_bayar number(5),
4 date_byr date
5 );
Table created.
[EMAIL PROTECTED]> create table calc (
2 id integer,
3 date_byr date,
4 cover varchar2(1),
5 amt number(5)
6 );
Table created.
[EMAIL PROTECTED]> insert into cover values (1,'A',1000);
1 row created.
[EMAIL PROTECTED]> insert into cover values (1,'B',500);
1 row created.
[EMAIL PROTECTED]> insert into cover values (2,'A',1000);
1 row created.
[EMAIL PROTECTED]> insert into cover values (2,'B',700);
1 row created.
[EMAIL PROTECTED]> insert into cover values (2,'C',300);
1 row created.
[EMAIL PROTECTED]> insert into cover values (2,'D',1500);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(1,1200,sysdate-10);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(1,300,sysdate-9);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,1000,sysdate-10);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,100,sysdate-9);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,300,sysdate-8);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,200,sysdate-7);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,300,sysdate-6);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,500,sysdate-5);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,800,sysdate-4);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,200,sysdate-3);
1 row created.
[EMAIL PROTECTED]> insert into bayar values
(2,100,sysdate-2);
1 row created.
[EMAIL PROTECTED]> commit;
Commit complete.
[EMAIL PROTECTED]> select * from cover order by 1,2;
ID C PREMIUM
--------------- - ---------------
1 A 1000
1 B 500
2 A 1000
2 B 700
2 C 300
2 D 1500
6 rows selected.
[EMAIL PROTECTED]> select * from bayar order by 1,3;
ID AMT_BAYAR DATE_BYR
--------------- --------------- ---------
1 1200 01-JAN-08
1 300 02-JAN-08
2 1000 01-JAN-08
2 100 02-JAN-08
2 300 03-JAN-08
2 200 04-JAN-08
2 300 05-JAN-08
2 500 06-JAN-08
2 800 07-JAN-08
2 200 08-JAN-08
2 100 09-JAN-08
11 rows selected.
[EMAIL PROTECTED]> select * from calc;
no rows selected
[EMAIL PROTECTED]> edit hitung.sql
[EMAIL PROTECTED]> @hitung
Enter value for id: 1
old 29:
where id = &id
new 29:
where id = 1
3 rows created.
[EMAIL PROTECTED]> @hitung
Enter value for id: 2
old 29:
where id = &id
new 29:
where id = 2
11 rows created.
[EMAIL PROTECTED]> l
1 insert into calc
2 select e.* from
3 (
4 select id, date_byr, cover,
5 hasil-nvl(lag(hasil) over (partition by
cover order by rn),0) amt
6 from
7 (
8 select rn, id, date_byr, cover,
9 case when amt_cum >= prem_cum then
premium
10 else
decode(sign(premium-(prem_cum-amt_cum)),-1,0,premium-(prem_cum-amt_cum))
11 end hasil
12 from
13 (
14 select b.*,
15 sum(amt_real) over (order by rn)
amt_cum,
16 sum(premium) over (partition by gid
order by rn) prem_cum
17 from
18 (
19 select rownum rn, a.*
20 from
21 (
22 select b.gid, b.id, b.amt_bayar,
b.date_byr, a.cover, a.premium,
23 case when b.gid = lag(b.gid)
over (order by b.gid)
24 then 0
25 else amt_bayar
26 end amt_real
27 from cover a,
28 (select i.*, rownum gid from
(select * from bayar
29
where id = &id
30
order by date_byr, amt_bayar desc) i) b
31 where a.id = b.id
32 order by b.gid, a.premium desc
33 ) a
34 ) b
35 ) c
36 ) d
37 ) e
38 where e.amt > 0
39* order by date_byr, cover
[EMAIL PROTECTED]> select * from calc where id = 1;
ID DATE_BYR C AMT
--------------- --------- - ---------------
1 01-JAN-08 A 1000
1 01-JAN-08 B 200
1 02-JAN-08 B 300
3 rows selected.
[EMAIL PROTECTED]> select * from calc where id = 2;
ID DATE_BYR C AMT
--------------- --------- - ---------------
2 01-JAN-08 D 1000
2 02-JAN-08 D 100
2 03-JAN-08 D 300
2 04-JAN-08 A 100
2 04-JAN-08 D 100
2 05-JAN-08 A 300
2 06-JAN-08 A 500
2 07-JAN-08 A 100
2 07-JAN-08 B 700
2 08-JAN-08 C 200
2 09-JAN-08 C 100
11 rows selected.
[EMAIL PROTECTED]> spool off;
--- risti <[EMAIL PROTECTED]> wrote:
> Temans, sy ada problem utk memecah data insert ke
> suatu table,
> sample casenya spt ini :
>
> cover table
> id Cover Premium
> 1 A 1000
> 1 B 500
>
> Bayar table
> ID Amt_bayar date_byr
> 1 1200 01-Jan-07
> 1 300 02-Jan-07
>
> insert ke calc table jadi spt ini :
> Cover amt date_byr
> A 1000 01-Jan-07
> B 200 01-Jan-07
> B 300 02-Jan-07
>
> gimana ya ngakalinnya?? jadi akan cek berapa
> amount covernya per cover, dan amount di table bayar
> akan dipecah2 sesuai maksimal dari amount di table
> bayar...
>
> thanks ya sblmnya
>
>
> ---------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
> [Non-text portions of this message have been
> removed]
>
>
Send instant messages to your online friends http://uk.messenger.yahoo.com
