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 2005 Forums

Transact-SQL (2005)

update query

Author

Topic

sefak169
Starting Member

6 Posts

Posted - 2009-09-25 : 01:46:04

database MS SQL 2005

i have a table having columns

Autoid AccountId Debit Credit Total running_total

The Following select Query which Gives me result for running totals.


SELECT 
	n.AutoId, 
	--n.order_total, 
	SUM(o.Linetotal) As running_total
FROM 


		(SELECT AutoId, 
            SUM(total) As Linetotal
        FROM Tb_AccountLedger WHERE ACCOUNTID = 1693
            GROUP BY AutoId ) n 

INNER JOIN

        (SELECT AutoId, 
            SUM(total) As Linetotal
        FROM Tb_AccountLedger WHERE ACCOUNTID = 1693
            GROUP BY AutoId 
            ) o 


            ON (n.AutoId >= o.AutoId)
GROUP BY n.AutoId
ORDER BY n.AutoiD

but i want the running field to be updated according to this query as i dnt want to run this query every time the user access.
i dnt wana use any loops already done tht its too slow
thx in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-25 : 02:10:15

Update?
In which destination column?

Sorry - didn't get you.

No, you're never too old to Yak'n'Roll if you're too young to die.

sefak169
Starting Member

6 Posts

Posted - 2009-09-25 : 02:53:03

the query gives me result for running totals but i need tht result to be updated is running_total column as

http://sefak169.0catch.com/at.gif
http://sefak169.0catch.com/RT.jpg
you can see the snap shot of the table and query above pls copy n paste the links to see the snap shots

i dnt know y img link doesnt work i dnt see the images i m trying to upload in preview so i m going to add simple text too
This is tb_account ledger table note tht running field is null
now i want to fill this column with the query which i ve already made
sorry if u ve to scroll to see all the results

autoid accountid debit credit total running_totals
1 843017 1693 0.0000000 2000.0000000 -2000.0000000 NULL
2 843018 1693 0.0000000 40.0000000 -40.0000000 NULL
3 843022 1693 0.0000000 120.0000000 -120.0000000 NULL
4 843023 1693 0.0000000 276.0000000 -276.0000000 NULL
5 843024 1693 0.0000000 193.0000000 -193.0000000 NULL
6 843034 1693 0.0000000 7000.0000000 -7000.0000000 NULL
7 843035 1693 0.0000000 7000.0000000 -7000.0000000 NULL
8 843036 1693 0.0000000 8000.0000000 -8000.0000000 NULL
9 843037 1693 0.0000000 6700.0000000 -6700.0000000 NULL
10 843038 1693 0.0000000 5000.0000000 -5000.0000000 NULL
11 843039 1693 0.0000000 7000.0000000 -7000.0000000 NULL
12 843040 1693 0.0000000 7200.0000000 -7200.0000000 NULL

Following is the result of the select query

AutoId running_total
1 843017 -2000.0000000
2 843018 -2040.0000000
3 843022 -2160.0000000
4 843023 -2436.0000000
5 843024 -2629.0000000
6 843034 -9629.0000000
7 843035 -16629.0000000
8 843036 -24629.0000000
9 843037 -31329.0000000
10 843038 -36329.0000000
11 843039 -43329.0000000
12 843040 -50529.0000000

Kabila
Starting Member

33 Posts

Posted - 2009-09-25 : 04:00:58

Try This:

declare @t numeric(9,2)
set @t=0
update tb_accountledger set @t=running_totals=@t+(total)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources