March 18, 2004 at 12:40 pm
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.
March 18, 2004 at 3:12 pm
Heres the problem in further detail.
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
-NS.
March 19, 2004 at 9:07 am
Well write a small procedure and call it. I hope this serves your purpose.
CREATE procedure MyProc
as
declare @rowno INT,
@amount NUMERIC(18,2),
@totalrowno INT
DECLARE MYCUR CURSOR FOR
SELECT rowno,
totalrowno
FROM
mytable
OPEN MYCUR
FETCH NEXT FROM mycur INTO @rowno,@totalrowno
WHILE @@FETCH_STATUS=0
BEGIN
IF @totalrowno <>0
BEGIN
UPDATE mytable SET amount =(ISNULL(amount,0.00)+(SELECT amount FROM mytable WHERE rowno=@rowno)) WHERE rowno=@totalrowno
END
FETCH NEXT FROM mycur INTO @rowno,@totalrowno
END
CLOSE MYCUR
DEALLOCATE MYCUR
Thanks
Prasad Bhogadi
www.inforaise.com
March 19, 2004 at 12:58 pm
You could alternatively write two update statements..
UPDATE #tbl SET
#tbl.Amount = #tbl.Amount + (SELECT Sum(T.Amount) FROM #tbl T WHERE T.RowNo < #tbl.RowNo)
WHERE #tbl.Type = 'S'
UPDATE #tbl SET
#tbl.Amount = #tbl.Amount + (SELECT Sum(T.Amount) FROM #tbl T WHERE T.Type = 'D')
WHERE #tbl.Type = 'F'
HTH...
March 19, 2004 at 5:02 pm
In this case, the types are not so well defined that I can do a two step update. AS in, The total at LineType 'F' need not be just a sum of all line types 'S'. It can even include some lines oif type 'D' and which are not added up in any 'S' type row.
-NS.
March 20, 2004 at 12:05 am
Hi,
Did you try the cursor that I posted?
Thanks
Prasad Bhogadi
www.inforaise.com
March 20, 2004 at 12:56 am
I tried this and it seems to work and do exactly what i want. however, one thing I am confused is declaring a cursor would lock those rows in the table. so you should not be able to update the rows. Please explain if i am mistaken on this issue.
Thanks,
-NS.
March 20, 2004 at 2:19 am
Please refer to DECLARE CURSOR and CURSOR LOCKING in BOL for a detailed explanation and understanding on locking and concurrency issues.
Thanks
-Prasad
Prasad Bhogadi
www.inforaise.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply