UPDATING RUNNING TOTAL - 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

General SQL Server Forums

Database Design and Application Architecture

UPDATING RUNNING TOTAL

Author

Topic

Miquash
Starting Member

1 Post

Posted - 2011-04-06 : 03:16:19

Dear Sir,

I'm using Visual Basic 2010 and my table in SQLserver 2008 my requirement to get running total. Detailed Table as follows...

DATE....DETAILS....DEBIT....CREDIT....RUNNINGTOT
1/1/11..Cash Bal................................5000
2/1/11..Sales......2000........................ ?
3/1/11..Rent Paid...........1000.............. ?
4/1/11..Purchase.............500.............. ?

5000 is my Opening Balance and the format should be like this...
Cash Bal 5000 + DEBIT - CREDIT = RUNNINGTOT so how can I update RUNNINGTOT column.

Thanks in advance.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 03:25:42

May be this will help you

Declare @t table (id int)--Assuming that This is you table name
insert into @t values (1550)--This Record is present in your table
Update @t set id=(select id+150 from @t) /*And this is what you are looking at Here @t is your table name and in select statement put all calculation columns : note you can update in different table column also*/
select * from @t

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-06 : 05:43:12

[code]
update t
set RUNNINGTOT = @opening_bal + TOTAL
from yourtable t
cross apply
(
select TOTAL = SUM(ISNULL(DEBIT, 0) - ISNULL(CREDIT, 0))
from yourtable x
where x.DATE <= t.DATE
) c
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-06 : 15:19:44

Calculate running totals on the client, not in sql. For a few articles about the topic and a great discussion followed one of articles mentioned visit: [url]http://mirko-marovic-eng.blogspot.com/2011/02/running-totals-in-sql-back-to-future.html[/url]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources