How to do rolling balances in Linq2SQL
- by David Liddle
Given an account with a list of transactions I would like to output a query that shows each transaction with the rolling balance (just like you would see on an online banking account).
TRANSACTIONS
- ID
- DATE
- AMOUNT
Here is what I created in T-SQL however was wondering if this can be translated to linq2sql code?
select
T.ID,
convert(char(10), T.DATE, 101) as 'DATE',
T.AMOUNT,
(select sum(O.AMOUNT) from TRANSACTIONS O
where O.DATE < T.DATE or (O.DATE = T.DATE and O.ID <= T.ID)) 'BALANCE'
from TRANSACTIONS as T
where T.DATE between @pStartDate and @pEndDate
order by T.DATE, T.ID
Alternatively I guess my other option is to just call a stored procedure for these kind of results. However, I have Services which call Repositories and didn't really want to put the sproc call in the Repository.
