February 5, 2003 at 11:10 pm
i have a table (tblTrx) with 3 fields: fldTrxDate, fldTrxType and fldAmount. i would like to generate a report wherein the balance is calculated and carried over to the next record in the report, as in the case of Bank statement or stock inventory.
Date TrxType Amount Balance
---------------------------------------
1/1/2003 Deposit 1000 1000
1/2/2003 Deposit 500 1500
1/3/2003 Withdrawal 600 900
...
How would i compute for the Balance column in a SELECT statement with its value being carried-over to the next record depending on TrxType? i would like to avoid having to use cursor or temp tables.
Thanks!
February 6, 2003 at 2:43 am
Presuming that your fldDate field is actually a datetime field and is unique you can use the following.
selectfldDate,
fldTrxType,
fldAmount,
(select sum(convert(money, case when fldTrxType = 'Withdrawal' then -1 else 1 end) * fldAmount)
from tblTrx
where fldDate <= trx.fldDate) as Balance
fromtblTrx trx
order by fldDate
Edited by - paulhumphris on 02/06/2003 02:44:03 AM
February 6, 2003 at 2:46 am
This is purely off the top of my head but wihtout using temp tables or cursors the way I would do it is to add another column, an identity column to the table
idcol Date TrxType Amount Balance
---------------------------------------
1 1/1/2003 Deposit 1000 1000
2 1/2/2003 Deposit 500 1500
3 1/3/2003 Withdrawal 600 900
then use the following SELECT as your source to update
SELECT *
FROM your_TABLE a join your_TABLE b
ON a.idcol = (b.idcol + 1)
Nigel Moore
======================
February 6, 2003 at 3:51 am
Yes, DateTime Field is unique for the Table. i have tried paulhumphris' SELECT statement and it worked as intended. Before, i use to handle the accumulation of Balance in VB front-end.
Thanks for the immediate response!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply