runningsum

Hi,

I have this problem. I need make a sql statment (not a SP) for this:

Example:

date amount
1/1 10
1/2 5
1/3 20

Now, my output is:

date amount
1/1 10
1/2 15 (5+10)
1/3 35 (15+15)

any idea? in access can make this with a runningsum,:eek: but, in SQL 2000?

TIA:beer:
Abel.I found a solution, with a subqueris, example
select date, amount, O.amount
,(select sum(amount) from <myTable>
where date <= O.date)
'runningSum'
from <myTable> O

another idea?
thanks.
Abel.|||Alternate method:

select TBL1.date,
sum(TBL2.Amount) as RunningSum
from [MyTable] TBL1
inner join [MyTable] TBL2 on TBL1.date >= TBL2.date
group by TBL1.date|||there are a couple of different sources that say a cursor (horrors!) is actually more efficient that set-based in the case of running totals:
http://www.sqlteam.com/article/calculating-running-totals
http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx

but I guess the fastest way is to do it in a CLR proc:
http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18309.aspx|||Thanks blindman and jezmine.

jezmine i read this link and tell to you. thanks again.

Running UPDATE statements in parallel on the same table

Hi all,

Does an UPDATE statment lock the entire table or just the rows that will be affected by the UPDATE?

I ask because -

Can I run UPDATE statements in parallel on the same table on the same column. The need for doing this is because the table is a large fact table. I plan to execute the same UPDATE statements on different time sections of the data to expedite the processing.

If the UPDATE statment lock the entire table then I cannot run an UPDATE in parallel. If the UPDATE statement just locks the rows that will be affected then maybe I can because rows affected will be different for each UPDATE.

Let me know.

Thanks,

VivekIt all depends on the lock escalation

Imaging if you ad hundereds of current connections and the lock always ascended to table locks..that wouldn't be a very good RDBMS, now would it.

BOL has a good description of this if you want to read up on it.