Cumulative Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cumulative Update Query

Author

Topic

nns80
Starting Member

9 Posts

Posted - 2004-03-18 : 16:50:57

I have the followig Table structure:

RowNo Type Amount TotalRowNo
----------------------------------------
1 D 10 5
2 D 20 5
3 D 30 5
4 D 40 5
5 S 0 8
6 D 60 8
7 D 70 8
8 S 0 9
9 F 0 0

I am trying to write an update query on the above table so that based on the total row no ... I add the total amount from each row put it in appropriate total row number and do this cumulatively till I reach the last row.

The Final table after all the updates should look like this.

RowNo Type Amount TotalRowNo
----------------------------------------
1 D 10 5
2 D 20 5
3 D 30 5
4 D 40 5
5 S 100 8
6 D 60 8
7 D 70 8
8 S 230 9
9 F 230 0

Thanks,
NS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 16:55:17

I don't understand your explanation or your result set. Could you explain in greater detail? It would also help if you provided the CREATE TABLE statement and INSERT INTO statements for your sample data. You'll find your question is answered faster when the poster provides that information.

Tara

nns80
Starting Member

9 Posts

Posted - 2004-03-18 : 17:08:16

Create Table #tbl
{
RowNo INT,
Type VARCHAR(2), -- This field is to identify the type of record(Detail, SubTotal, FinalTotal)
Amount MONEY,
TotalRowNo INT
}

Example:
Insert INTO #tbl
VALUES (1,'D',10.00,5)

The situation is: Each row has a Total Row Number which indicates where its amount should be added to. example in the first row (rowno = 1), it says that the total row number is 5. Hence amount is to be added to the amount on row number 5. This goes on for each and every row. Even the new amount on row number 5 gets added to the amount on row number 8 and so on till you reach the last row of this temporary table.

Example:
After Processing Row 1: amount on row 5 = 0 + 10 = 10
After Processing Row 2: amount on row 5 = 10 + 20= 30
After Processing Row 3: amount on row 5 = 30 + 30 = 60
After Processing Row 4: amount on row 5 = 60 + 40 = 100
After Processing Row 5: amount on row 8 = 0 + 100 = 100
... and so on
.... and so on

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 18:14:12

[code]update #tbl
set amount = d.s_amount
from #tbl t,
(
select rowno,type,(select sum(amount) from #tbl where type = 'D' and rowNo <=t.rowno) s_amount
from #tbl t
) d

where d.rowno = t.rowno
and t.type IN ('S','F')

select * from #tbl[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources