I'm looking for a good approach for calculating and caching periodic opening account balances using SQL.
I know I can use SQL to sum up a bunch of transactions from start of time to the start of the period in question, but I'm more interested in whether its worth caching those calculated balances (at various points in time) in another table? Obviously this would require invalidating that table when older transactions are edited.
I'm just not sure if it's worth the hassle, if there's an easier way to do it, or just some general advice on the issue.
Assume many of transactions though mostly spread across many accounts, so many transactions would be filtered out of the calculation. I'm using SQL Server 2008 and OK to use features specific to it.
Currently I'm using:
public Decimal GetAccountBalance(DateTime forDate)
{
var sum=(from t in m_dc.Transactions
where t.date<forDate && t.account_id==m_CurrentAccount.id
select (decimal?)t.amount).Sum();
return sum==null ? 0 : (decimal)sum;
}