December 4, 2006 at 6:42 pm
I am trying to select everything out of my journal table and store it in a temporary file. In that temporary file I would like to take each transaction and keep a running balance. What I means is, I would like to add or subtract the balance from a total that will start at $0 and change with each transaction. I would also like to be able to re-add a previous transaction back to the balance if it has a NSF fee transaction after it. In the code below I have been able to re-display each transaction as a 'Running Balance', but it doesn't add. I can't find anything in SQL Server that will give me the ability to re-add the previous transaction. Is this possible?
set nocount on
IF OBJECT_ID('tempdb..#TEMP37') is not null
BEGIN
drop table #TEMP37
END
Declare @C_NSF_FeeUseFlagID AS INT
SET @C_NSF_FeeUseFlagID = 94
Declare @C_RunningBal AS INT
SET @C_RunningBal = 0
Declare @C_NSF_Fee AS Money
SET @C_NSF_Fee = 20.00
Declare @RowCnt
SET @RowCnt = @@ROWCOUNT
SELECT DISTINCT
cv.contactIdnt AS "ID"
, cv.credNumber AS Credential
, cv.contactName AS "Account"
, th.transactionType AS "Type"
, cast (th.reference as varchar(50) ) AS "Reference"
, j.remark AS "Control Number"
, ( - j.amount )AS "_$_Amount"
, j.transactiondate AS "_d_Trans. Date"
, j.entrydate AS "_d_Entry Date"
, j.depositdate AS "_d_Deposit Date"
, cast( th.BatchNumber as varchar(12) ) As "Batch Number"
, cv.divisionIdnt AS "_h_divisionIdnt"
, (-j.amount) + @C_RunningBal AS "Running Balance"
IF J.AMOUNT not null
set @C_RunningBal = (-j.amount) + @C_RunningBal
INTO #TEMP37
FROM Journal j
LEFT JOIN TransactionHeader th
ON j.TransactionHeaderIdnt = th.TransactionHeaderIdnt
LEFT JOIN CredentialView cv
ON cv.credentialIdnt= j.credentialIdnt
WHERE j.transactiondate
BETWEEN '20060101'
AND '20061201'
AND j.recordType = 'CR Summary'
UNION ALL
Select
cv.contactIdnt AS "ID"
, cv.credNumber AS Credential
, cv.contactName AS "Account"
, 'NSF' AS "Type"
, 'N/A' AS "Reference"
, j.remark AS "Control Number"
, ( - j.amount ) AS "_$_Amount"
, j.transactiondate AS "_d_Trans. Date"
, j.entrydate AS "_d_Entry Date"
, j.depositdate AS "_d_Deposit Date"
,'N/A' As "Batch Number"
, cv.divisionIdnt AS "_h_divisionIdnt"
,( -j.amount + @C_RunningBal) AS "Running Balance"
From Journal J
INNER JOIN CredentialView cv (NOLOCK)
On cv.CredentialIdnt = J.CredentialIdnt
INNER JOIN CredentialDefinition cd (NOLOCK)
ON cv.credentialDefinitionIdnt = cd.credentialDefinitionIdnt
INNER JOIN FeeDefinition fd (NOLOCK)
ON j.FeeDefinitionIdnt = fd.FeeDefinitionIdnt
AND fd.Active = '6'
INNER JOIN FeeUseFlagType fuft (NOLOCK)
ON fd.feeUseFlagID = fuft.feeUseFlagID
AND fuft.feeUseFlagID = @C_NSF_FeeUseFlagID
WHERE j.transactiondate
BETWEEN '20060101'
AND '20061201'
AND j.recordType = 'Fee'
ORDER BY th.transactionType, cv.credNumber
SELECT * FROM #TEMP37
December 6, 2006 at 10:19 am
This article might help:
http://www.databasejournal.com/features/mssql/article.php/3112381
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply