update @var=col=new_value

Question

i have table Account(operationtype,value,date), where operationtype = deposit or withdrawal. after each operation performed, i want to display the current amount of money in the account. i already solved this in 2 ways, using a cursor and a select stmnt, which both work fine, but i got stuck when i tried to solve it using an update and now it bugs me that i can't figure it out. my update is: DECLARE @amntMoney money SET @amntMoney = 0 UPDATE Account SET @amntMoney = value = @amntMoney + (CASE WHEN operationtype = 'deposit' THEN value ELSE -value END)  this would update the 'value' column in the table to display the current amount of money in the account; the problem is that i don't know how or where to place an 'order by date clause', so that the update stmnt takes the records in order, from the first date to the last date in the table. 'll provide some test data: (withdraw,70,2-1-2010) (deposit,100,1-1-2010) (deposit,50,1-3-2010) results: (deposit,100,1-1-2010) (deposit,150,1-3-2010) (withdraw,80,2-1-2010) how can i do this? thanks

Answer 1

create table t (operationtype varchar(50), value money, dt datetime)
insert into t
select 'withdraw', 70, '20100201'
union all
select 'deposit', 100, '20100101'
union all
select 'deposit', 50, '20100103'

CREATE TABLE #T (ID INT IDENTITY, operationtype varchar(50), value money, dt datetime)
INSERT INTO #T
SELECT *
FROM t
ORDER BY dt

 

DECLARE @amntMoney money
SET @amntMoney = 0

UPDATE #t
SET @amntMoney = value = @amntMoney +
(CASE WHEN operationtype = 'deposit' THEN value ELSE -value END)

UPDATE t1
SET t1.value=t2.value
FROM t t1 INNER JOIN #T t2
ON t1.dt=t2.dt

DROP TABLE #t

select * from t order by dt

---

http://www.t-sql.ru

Answer 2

or use CURSOR

---

http://www.t-sql.ru

Answer 3

You need to first create a new temp table ordered by date and using clustered primary key, e.g.

create table Temp (ID int identity primary key, Type varchar(20), Amount decimal (10,2), DateFld date)

insert into Temp select * from myTable order by DateFld

and then use update for this table and based on this you may update your original.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Also asked and answered here in detail
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143556

 

Answer 5

and read this: http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/06/cumulative-totals-screencast.aspx

---

http://www.t-sql.ru

Answer 6

thanks a lot, Alexey Knyazev, that worked beautifully. guess i wasn't thinking outside the box (using a temp table).

i had solved the problem using a CURSOR and also with a select stmn, i just wanted to solve it using UPDATE too.

followed your link, thanks again.

 

@Naom - thank u :)

@Peso - no one mentioned this solution (use of temp tables) on the other forum, thanks though

Answer 7

As said before, even if the data is in a temp table, there is no guarantee it will work.
However, as extensive tests has been done to break it without success, unlesss any of these conditions are met.

1) The clustered index is not the same "order" as the wanted running total
2) Partitioning is considered
3) Parallellism is considered

Answer 8

You may take a look at the more lengthy thread on this topic Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?

I've been thinking about writing a WiKi topic on the Running Total problem showing all known solutions with the links.

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 9

that'd be a great idea.. if u do write a topic, please let me know :)

i'm reading that link u posted, if any questions / statements, i'll come back with a reply here :)

again, thanks for your time & patience

Answer 10

Just want a verification - where quirky update is documented?

Say, am I correct in my re-write? (I comment out the original code):

		update Specials set @nNextVal = last_mod = @nNextVal + 1 where Name in (select Name from Inserted)
    /*  
		declare key_curs cursor for
		 select name from inserted
		open key_curs
		fetch key_curs into @cKeyVal
		while @@fetch_status = 0
		begin
			update specials set last_mod = @nNextVal 
			  where name=@cKeyVal
			if @@ERROR != 0
				begin
					raiserror('Cannot update the LAST_MOD value.',16,1)
					close key_curs
					deallocate key_curs
					return
				end
			select @nNextVal = @nNextVal + 1
			fetch key_curs into @cKeyVal
		end
		close key_curs
		deallocate key_curs
  */

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog