| Update Set of Records [message #23508] |
Fri, 13 December 2002 13:36  |
Prabhu
Messages: 26
Registered: October 2001
|
Junior Member |
|
|
Sample data
EmpCode Date Balance Accts
102 11/1/02 533777.63 102
102 11/4/02 533777.63 112
102 11/5/02 533777.63 122
102 11/6/02 533709.89 132
102 11/7/02 533764.19 152
102 11/8/02 533764.2 172
102 11/11/02 533764.2 202
102 11/12/02 533764.2 222
102 11/13/02 533764.2 242
102 11/14/02 539904.2 262
102 11/15/02 539904.2 292
102 11/18/02 539904.2 302
102 11/19/02 539904.2 332
102 11/20/02 539904.2 382
102 11/21/02 541844.54 432
102 11/22/02 541872.84 444
102 11/25/02 542592.58 520
102 11/26/02 543807.58 545
102 11/27/02 543807.58 555
102 11/28/02 543807.58 567
102 11/29/02 543872.29 678
tha missing rows in between data records represents
weekends (refer date column).
Now i need to update those missing rows with
the previous working day's data.
i.e for 11/2/02 and 11/3/02 i need to take
the same data that of 11/1/02 and update AND
for 11/9/02 and 11/10/02 it should be
11/8/02's data and so on.
Final output shud be like ,
for 11/2/02 and 11/3/02
EmpCode Date Balance Accts
102 11/1/02 533777.63 102
102 11/2/02 533777.63 102
102 11/3/02 533777.63 102
How to go about this ?
|
|
|
|
| Re: Missing rows? [message #23509 is a reply to message #23508] |
Fri, 13 December 2002 14:00  |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
What do you mean by missing rows?
You mean to say that there are rows but all the columns are null? I would assume the row would atleast have empcode, if not it becomes an INSERT not UPDATE.
Pl. clarify.
Thx,
Sri
|
|
|
|
| Re: Insert set of records [message #23513 is a reply to message #23508] |
Fri, 13 December 2002 16:04 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Here is one way of doing it.
SQL> select * from accts;
EMPCODE LU_DATE BALANCE
--------- --------- ---------
1 01-NOV-02 100
1 04-NOV-02 110
1 05-NOV-02 50
1 06-NOV-02 60
1 07-NOV-02 66
1 08-NOV-02 80
1 11-NOV-02 111
7 rows selected.
SQL> begin
2 for rec in
3 (
4 select x.dt2 from
5 (select a.dt + rownum - 1 dt2 from
6 (select min(lu_date) dt from accts) a, user_tables b) x
-- you can replace user_tables table
-- with something in your db with suffient number of rows
7 where x.dt2 <= (select max(lu_date) from accts)
8 minus
9 select lu_date from accts
10 )
11 loop
12 insert into accts
13 select empcode, rec.dt2, balance from accts where lu_date =
14 (select max(dt) from
15 (select lu_date dt from accts where lu_date <= rec.dt2)
16 );
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL> select * from accts;
EMPCODE LU_DATE BALANCE
--------- --------- ---------
1 01-NOV-02 100
1 04-NOV-02 110
1 05-NOV-02 50
1 06-NOV-02 60
1 07-NOV-02 66
1 08-NOV-02 80
1 11-NOV-02 111
1 02-NOV-02 100
1 03-NOV-02 100
1 09-NOV-02 80
1 10-NOV-02 80
11 rows selected.
Look at the last four rows, they are inserted with
correct data from thier most previous day.
Thx,
SriDHAR
|
|
|
|
| Re: Update Set of Records [message #23515 is a reply to message #23508] |
Fri, 13 December 2002 18:45 |
jiltin
Messages: 44
Registered: September 2002
|
Member |
|
|
Create the table like this. Load your data. Execute this pl/sql procedure. This script will work.
CREATE TABLE ACCT_INFO (
EMPCODE NUMBER (3),
ACCT_DATE DATE,
BALANCE NUMBER (15,2),
ACCTS NUMBER (3));
declare
old_acct_date acct_info.acct_date%TYPE ;
old_empcode acct_info.EMPCODE%TYPE ;
old_balance acct_info.balance%TYPE ;
old_accts acct_info.accts%TYPE ;
cursor acct_cursor is
select empcode,acct_date,balance,accts from acct_info
order by acct_date,empcode,balance,accts;
begin
select min(acct_date)-1 into old_acct_date from acct_info; -- Get the lowest date
select empcode,balance,accts into
old_empcode,old_balance,old_accts --initialize
from acct_info where acct_date = old_acct_date+1;
for c1 in acct_cursor
loop
if (trunc(c1.acct_date - 1) != old_acct_date ) then -- regular weekday
insert into acct_info (
empcode,acct_date,balance,accts )
values (
old_empcode,old_acct_date+1,old_balance,old_accts );
insert into acct_info (
empcode,acct_date,balance,accts )
values (
old_empcode,old_acct_date+2,old_balance,old_accts );
end if;
old_acct_date := c1.acct_date;
old_empcode := c1.empcode;
old_balance := c1.balance;
old_accts := c1.accts;
end loop;
commit;
end;
|
|
|
|
| Re: Insert set of records - another conventional approach [message #23526 is a reply to message #23513] |
Sun, 15 December 2002 09:12 |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
SQL> select * from accts;
EMPCODE LU_DATE BALANCE
--------- --------- ---------
1 01-NOV-02 100
1 04-NOV-02 110
1 05-NOV-02 50
1 06-NOV-02 60
1 07-NOV-02 66
1 08-NOV-02 80
1 11-NOV-02 111
7 rows selected.
SQL> declare
2 v_min_dt date;
3 v_max_dt date;
4 v_cnt number := 0;
5 begin
6 select min(lu_date) into v_min_dt from accts;
7 select max(lu_date) into v_max_dt from accts;
8 while v_min_dt < v_max_dt loop
9 select count(*) into v_cnt from accts
10 where lu_date = v_min_dt;
11 if v_cnt = 0 then
12 insert into accts
13 select empcode, v_min_dt, balance from accts
14 where lu_date =
15 (select max(dt) from
16 (select lu_date dt from accts where lu_date <= v_min_dt));
17 end if;
18 v_min_dt := v_min_dt + 1;
19 end loop;
20 end;
21 /
PL/SQL procedure successfully completed.
SQL> select * from accts;
EMPCODE LU_DATE BALANCE
--------- --------- ---------
1 01-NOV-02 100
1 04-NOV-02 110
1 05-NOV-02 50
1 06-NOV-02 60
1 07-NOV-02 66
1 08-NOV-02 80
1 11-NOV-02 111
1 02-NOV-02 100
1 03-NOV-02 100
1 09-NOV-02 80
1 10-NOV-02 80
11 rows selected.
thx,
Sri
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
