Running Total
-
Hi All, need help getting a running total please!
I have a table that has a list of transaccions, not a huge table, there will probably be a couple 100 records a month or so, and I am not sure whether or not to calculate a running total and save it in the table for each transaction or run a query to get the date every time. What are the advantages?
My table looks something like this: Transaction(TransactionID, TransactionDate, Description, Amunt, Balance)
I decided to calculate the running total every time I needed as opposed to save it into the database.
I found the following scripts that works perfectly based on the primary ID of the table:
SELECT a.TransactionID, a.Amount , SUM(b.Amount) AS RunningTotal FROM [Transaction] a
JOIN [Transaction] b ON b.TransactionID <= a.TransactionID GROUP BY a.TransactionID , a.Amount
ORDER BY a.TransactionID, a.Amount
The problem is that transactions will not come in the order of insertion but by the TransactionDate. There could be transactions inserted today that have an effective day (TransactionDate) of 2 months ago.
I need to get a running bassed on TransactionDate and secondarly by TransactionID. So I tried the following and it didn't work!
SELECT a.TransactionDate, a.Amount , SUM(b.Amount) AS RunningTotal FROM [Transaction] a
JOIN [Transaction] b ON b.TransactionDate <= a.TransactionDate GROUP BY a.TransactionDate , a.Amount
ORDER BY a.TransactionDate, a.Amount
Please help!
-
There are few discussion on this subject earlier on this forum
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=108279
You can search there are few more discussions as well
My Blog:
http://dineshasanka.spaces.live.com/ -
Thanks.
I was able to fix my issue as follows:
select
A.TransactionID,
A.TransactionDate,
A.Amount,
(select sum(amount) from [Transaction] B
where B.TransactionDate < A.TransactionDate OR
(B.TransactionDate = A.TransactionDate and B.TransactionID <= A.TransactionID)
) as RunningTotal
from
[Transaction] A Order By TransactionDate, TransactionID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply