CASE statement to increment trhough record and add to balance

[Ty1]

I am new to SQL Server. I am trying to increment through my journal records and keep a 'Running Balance' while incrementing through each record. I also need a CASE statement to re-add a transaction if it had a 'NSF' fee. My code is below. So far I have attempted the CASE statement, but it isn't working.... Any help is appreciated. Thanks!

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


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"
, @C_RunningBal = CASE
WHEN (select j.description from Journal
WHERE Journal.Description = 'Bad Check Fee'
Then select sum(-j.amount) + @C_RunningBal AS "Running Balance"
--WHERE Journal.Description = 'NSF (Bad Check)'
--Then (sum(-j.amount) + @C_RunningBal) AS "Running Balance"
--WHERE Journal.Description = 'NSF Check'
--Then (sum(-j.amount) + @C_RunningBal) AS "Running Balance"
END
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

 

[AlexCuse]

You should post your SQL code between these tags [ignore]

[/ignore]. That will make it much easier to read.

Is your problem in the calculation of running balance? I think you want to place your data in a temp table, and use a while loop to go through and update running balance in the table.

What field you order by when selecting into #TEMP37? You want your data in such an order that row3 comes after row2 comes after row1, not just in the results, but chronologically as well. If we can do this, then it will be easy to assign running balance.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ty1]

Thanks AlexCuse,
I am having a problem with calculation of the running balance in that it doesn't change with each record it just total's all records and gives the result at the end of each row. Also, I am placing it in a Temp table. How can I implement the while loop? Also, I'm not ordering it at all, but I suppose I could order by 'ID', but some records are from the same ID how can SQL keep track? Thanks!

 

[AlexCuse]

Basically, what we want to do is create a field 'RunningSum' in your table. Where you are currently selecting running sum, simply select this:

null as RunningSum

Then, the key really becomes in ordering your records as they are inserted to your temp table. I think the best option is probably "_d_Trans. Date"

Another question: do you want runningsum to be calculated by Account, or do you want it to be the sum of all records in your table?

Once we've got this established, we will be able to work on a solution.

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[Ty1]

Yes, I would like to have running sum to be calculated by all records in the table.

 

[rac2]

@C_RunningBal = CASE
WHEN (select j.description from Journal
WHERE Journal.Description = 'Bad Check Fee'
Then select sum(-j.amount) + @C_RunningBal AS "Running Balance"
--WHERE Journal.Description = 'NSF (Bad Check)'
--Then (sum(-j.amount) + @C_RunningBal) AS "Running Balance"
--WHERE Journal.Description = 'NSF Check'
--Then (sum(-j.amount) + @C_RunningBal) AS "Running Balance"
END

Take a look at Books Online topic CASE. In Transact-SQL CASE is a function, not a statement as in procedural programming. Review the syntax.

It appears that the value you wish to test is in a column in the table Journal, so the subquery is not necessary.

Try

@C_RunningBal = CASE
    WHEN j.description = 'Bad Check Fee'
      THEN -j.amount + @C_RunningBal
    WHEN j.Description = 'NSF (Bad Check)'
      THEN -j.amount + @C_RunningBal
    WHEN j.Description = 'NSF Check'
      THEN -j.amount + @C_RunningBal
    ELSE @C_RunningBal
END AS "Running Balance"

I have never tried obtaining a running balance in this way, interesting idea.

Dont see why you were using the aggregate function SUM(-j.amount).

 

[AlexCuse]

ty1 - let me know if rac2's suggestion gets you what you want. If it does, then no need to work with the loop. I'm not sure that would return a true running balance, as I understand the term.

If it does not, let me know what you want runningsum to start at (zero?)

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ty1]

I have tried three ways:

@C_RunningBal = CASE
WHEN (select j.description from journal
WHERE j.description = 'Bad Check Fee'
THEN select sum(-j.amount) + @C_RunningBal AS "Running Balance"
WHERE j.Description = 'NSF (Bad Check)'
THEN (sum(-j.amount) + @C_RunningBal) AS "Running Balance"
WHERE j.Description = 'NSF Check'
THEN (SUM(-j.amount) + @C_RunningBal) AS "Running Balance"
END

--and---


@C_RunningBal = CASE
WHEN j.description = 'Bad Check Fee'
THEN -j.amount + @C_RunningBal
WHEN j.Description = 'NSF (Bad Check)'
THEN -j.amount + @C_RunningBal
WHEN j.Description = 'NSF Check'
THEN -j.amount + @C_RunningBal
ELSE @C_RunningBal
END AS "Running Balance"

--and--

@C_RunningBal = CASE
WHEN j.description = 'Bad Check Fee'
THEN -j.amount + @C_RunningBal AS "Running Balance"
WHEN j.Description = 'NSF (Bad Check)'
THEN -j.amount + @C_RunningBal AS "Running Balance"
WHEN j.Description = 'NSF Check'
THEN -j.amount + @C_RunningBal AS "Running Balance"
END


I get error messages with all three. The first set up says "Incorrect syntax near the keyword 'Then'. For the first two 'Thens'. The second and third says "Incorrect syntax near the keyword 'AS'. I have looked in my 'SQL Server 2005 Programming' book and it has it set up much like the first one. I'm not sure why the syntax is wrong.

 

[AlexCuse]

selecting @C_RunningBal is the same as selecting AS "Running Balance" (probably not exactly, but in this case I believe it is). If you remove one or teh other then I think the query will run.

Let me know if it works, otherwise I've got a half-baked notion that I can throw back in the oven.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

I think the main problem is your use of double quotes (") on the Running Balance alias. T-SQL doesn't use double quotes generally, and never any quotes with an alias name.

Running Balance should be listed after the END with a END as RunningBalance. T-SQL doesn't like separated words either, so your options for your column name are:

END as RunningBalance

END as Running_Balance

END as [Running Balance]  -- [b]NOT recommended[/b]

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Ty1]

Alex,
When I run it this way:
@C_RunningBal = CASE
WHEN j.description = 'Bad Check Fee'
THEN -j.amount + @C_RunningBal
WHEN j.Description = 'NSF (Bad Check)'
THEN -j.amount + @C_RunningBal
WHEN j.Description = 'NSF Check'
THEN -j.amount + @C_RunningBal
ELSE @C_RunningBal
END

I get this message: A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

 

[Ty1]

Running it with:

END as RunningBalance

END as Running_Balance
gives me this message: "Incorrect syntax near the keyword AS

 

[rac2]

CatAdmin-
Regarding aliases, Books Online example

SELECT FirstName + ' ' + LastName AS "Employee Name", 
       IDENTITYCOL AS "Employee ID",
       HomePhone,
       Region,
       10 AS Constant
FROM Northwind.dbo.Employees
ORDER BY LastName, FirstName ASC

 

[AlexCuse]

I get this message: A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

You just want to assign your value to @C_RunningBal before your select into your table. You need to just select this:

CASE
    WHEN j.description = 'Bad Check Fee'
      THEN -j.amount + @C_RunningBal
    WHEN j.Description = 'NSF (Bad Check)'
      THEN -j.amount + @C_RunningBal
    WHEN j.Description = 'NSF Check'
      THEN -j.amount + @C_RunningBal
    ELSE @C_RunningBal
END as RunningBalance

I still don't know if this is going to return the RunningSum that you want, but try it out and post back.

Ignorance of certain subjects is a great part of wisdom

 

[rac2]

Ty1-

The error message regarding what is permissable in a SELECT INTO is probably the key to the problem. It suggests that your idea of accumulating with the variable @C_RunningBal is not going to work.

Alternatives might be a stored procedure and a loop as AlexCuse seems to be suggesting; or a VIEW with intermediate results which can be used in a mutli-query stored procedure; or a sub-query that aggregates the amount for each row. This last would be inefficient for a large number of rows.

Sounds like AlexCuse is cooking so I will bow out of this thread.

 

[AlexCuse]

rac2 - I like your idea to use a view with a stored proc. What do you think the performance of that would be like vs. using a while loop? This may be a better solution, I just can't seem to get my head around how to assign the sum that way (without using your last suggestion, the performance killer). I may be cooking, but I'm always looking for a way to make the results more edible ;-) So by all means, don't bow out.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

First, running total can be seen as a presentation issue. How about displaying the running total in the front end and not worrying about the server generating it? This will be the lowest overhead of any other method for calculating this amount.

Second, it might help to separate the running total calculation from the extra rows you need for NSF. Try this:

Insert all your records into a temp table as you are doing, without trying to calculate the fees for NSF yet. Include a running total column cast(NULL as decimal(15,2)) or whatever.

Insert into the temp table the rows for your NSF fees, using appropriate trandates and other columns.

Update your temp table with correct running totals. The only way you can do this reliably with very large datasets is either a cursor, or a correlated subquery, or a derived table self-join. If you try to use a @variable update with an ORDER BY clause to force update order, it may work for a long time until you one day get a dataset that is so big it's split into multiple work pieces by the server. Then you're in trouble.

Here's an idea for you, the derived table method:

UPDATE T
SET T.[Running Balance] = X.TranSum
FROM
   #TEMP37 T
   INNER JOIN (   
      SELECT
         T1.TempID,
         TranSum = Sum(
            CASE
            WHEN T2.Description IN ('Bad Check Fee', 'NSF (Bad Check)', 'NSF Check')
               THEN -T2.[_$_Amount]
            ELSE T2.[_$_Amount]
            END,
         )
      FROM
         #Temp37 T1
         INNER JOIN #Temp37 T2 ON T1.transactiondate >= T2.transactiondate AND T1.TempID >= T2.TempID
      GROUP BY
         T1.TempID
   ) X ON T.TempID = X.TempID

There's a larger issue here, though. Why would NSF fees only live in a temp table? Or is this the method by which they are getting generated, posting from a journal into the 'real' account?

First of all, make sure you make this data driven. Instead of setting the fee to some amount in code, put these into a table. The last thing you want to do is update code every time the fee changes.

There is so much else going on here... some really important choices are in the works here and there's a lot of possibility for making poor ones that will severely and negatively impact the system down the road.

Step back and help us understand what it is that you're doing from a larger picture!

 

[Ty1]

Thanks AlexCuse, Rac2, CatAdmin, and ESquared for all of your help. I agree, that this will probably be better done with a stored procedure, however, I have not written a stored procedure in SQL before, so forgive me for thinking that this is what I was doing. Any suggestions? Also, to answer your questions, Esquared, NSF fee are not only in the temp table, but that is where I thought I could do calculations to obtain a more accurate balance by re-adding transaction to the balance that were deducted before a NSF fee was charged. I just can't seem to do that. You are right, I am calculating a total balance in the front-end of the application, however, this does not include the transaction that was returned as NSF. Instead of re-charging the customer for the transaction, it just charges a NSF fee and gives a balance (including the transaction that was deducted before it was returned by the bank).

 

[Catadmin]

A stored procedure is simply a set of T-SQL code/queries that can take input and give output. Look up CREATE PROCEDURE in Books Online.

It's actually not that much harder than writing the query itself. You just have to remember to properly declare all your variables and that not all T-SQL commands (Alter Table is one) works inside of a Proc.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"