sql server - About sql server2008 running total -

sql server - About sql server2008 running total -

i have table , table construction is:

transactionrecordid state transactionmoney createtime 1 1 200 2014/6/19 2 0 100 2014/6/12 3 1 50 2014/3/17 4 1 400 2014/2/23 ......

i want result:

transactionrecordid state transactionmoney createtime totalmoney 1 1 200 2014/6/19 650 2 0 100 2014/6/12 450 3 1 50 2014/3/17 450 4 1 400 2014/2/23 400

if current record field state=1,i sum transactionmoney after current

record add together current transactionmoney , else sum transactionmoney after current record

sorry english!

sql server 2012 makes lot simpler using sum() on (order ...), in sql server 2008 can utilize left bring together same effect;

select a.transactionrecordid, a.state, a.transactionmoney, a.createtime, coalesce(sum(b.transactionmoney), 0) totalmoney transactions left bring together transactions b on a.createtime >= b.createtime , b.state = 1 grouping a.transactionrecordid, a.state, a.transactionmoney, a.createtime order a.transactionrecordid;

an sqlfiddle test with.

sql-server sql-server-2008