I have a Sqlite database with a table containing financial transactions. Pertinent columns are date, PO#, and debit and credit amounts. I need to display these transactions and have a running total calculated based on an arbitrary starting balance (which is always the first record).
I created a view based on this query:
SELECT t1.[ID], t1.[Date], t1.[PO], t1.[Debit], t1.[Credit],
( SELECT SUM( t2.[Credit] ) - SUM ( t2.[Debit] )
FROM [Transaction] t2
WHERE t2.[ID] <= t1.[ID] ) AS [Balance]
FROM [Transaction] t1
and it works. Problem is, sometimes I need to order by date and/or PO# and when I do, while the Balance column value is correct, it's out of order. Also, I'll need to filter by date, so I don't believe I can use a computed column in the Transaction table.
I've looked into using the ROWID value, but no change. As far as I can find, Sqlite doesn't support ROW_NUMBER() OVER(). I've spent a couple days trying to wrap my head around this with no luck.
This a .NET Compact Framework app, Sqlite ADO.NET version 1.0.66 (not sure what version of the Sqlite engine that is).
The solution was indeed to add a Balance field to the table, and update it via triggers when records are added/edited/deleted. Turns out my real question wasn't so much how to do a running total, but best practice for doing what I was trying to do, which was a banker's database as suggested by MPelletier. I found examples of the triggers I needed in this thread which, with a little tweaking, I got to do what I needed. Consequently, I no longer require the view since its only purpose was for the running total.