|
Andraax
Aged Yak Warrior
790 Posts |
 Posted - 2001-11-01 : 04:20:35
|
| Hi!I'm writing a procedure to populate a position table for a mutual fund system, given a transaction table. I will simplify the tables to illustrate it here:create table tmp_trans(transdate datetime not null,fund varchar(10) not null,poid int not null,qty float not null)create table tmp_pos(posdate datetime not null,fund varchar(10) not null,poid int not null,qty float not null)Here is some sample data for the transaction table:insert tmp_trans values ('2001-10-01', 'FUND1', 1, 5.0)insert tmp_trans values ('2001-10-01', 'FUND2', 1, 6.0)insert tmp_trans values ('2001-10-01', 'FUND3', 1, 6.0)insert tmp_trans values ('2001-10-02', 'FUND1', 1, 7.0)insert tmp_trans values ('2001-10-02', 'FUND2', 1, 6.0)insert tmp_trans values ('2001-10-02', 'FUND3', 1, 5.0)insert tmp_trans values ('2001-10-01', 'FUND1', 2, 1.0)insert tmp_trans values ('2001-10-01', 'FUND2', 2, 2.0)insert tmp_trans values ('2001-10-01', 'FUND3', 2, 3.0)insert tmp_trans values ('2001-10-02', 'FUND1', 2, 4.0)insert tmp_trans values ('2001-10-02', 'FUND2', 2, 5.0)insert tmp_trans values ('2001-10-02', 'FUND3', 2, 6.0)The transaction (tmp_trans) table now contains transactions for 2 protfolios, 2 dates and 3 funds. The position table (tmp_pos) should be filled up with the positions for the portfolios in each fund. For one date it would look like this:select <date>, fund, poid , sum(qty)from tmp_transwhere transdate <= <date>group by <date>, fund, poidThe position table should be filled up with positions for every date with a transaction. The only way I have come up with is using a cursor for the date and then using the query above to fill the position table one day at a time, but for all portfolios. The transaction table in reality contains about 500 000 rows.Is it possible to do this without cursors??Edited by - Andraax on 11/01/2001 04:22:58 |
|
Cursor or not 2