Single PROC / 3 Conditions / Multiple AND's with IF statement
-
Hi everyone -
Fairly new SQL Developer, and am trying to see if I can use an IF statement with multiple AND conditions in it.
IF @CalcBalance0 < 0.00 AND @GeneralOverdraft0 IS NULL AND @OverDraftAccountID0 IS NULL
Or if I can do it like this. . .
IF @CalcBalance1 < 0.00 AND @GeneralOverdraft1 IS NULL AND @OverDraftAccountID1 IS NOT NULL
Sorry for the lack of additional code to all of this, but just assume I am following normal conventions here after. Is it possible for me to form my IF statement this way? And if not - how can I put this in one PROC to satisfy 2 possibilities (using ELSE) for 3 different sets of conditions?
Thank you!
-
Yes, it's possible. The statement after IF "is an expression that returns TRUE or FALSE." (straight from BooksOnLine, the SQL Server help system usually installed with SQL Server).
Another option is using a set of CASE statments when a conditional handling is needed within a query. This might be useful when replacing loops (e.g. c.u.r.s.o.r.) with a set based solution.
-
Lutz - thank you for your reply. I had initially thought of trying to treat it as a SELECT CASE, but I am still trying to figure out all the logic. This is a doozy of a PROC for a study project I am doing for a class that I am in. It basically is trying to weigh conditions for a given bank account, and determine which to use for that account's given scenario (Account with no Overdraft protection, Account with General Overdraft, Account with Specific Overdraft, Account with both General and Specific available Overdraft). Definitely SELECT CASE worthy, and I may eventually wind up with that as my final code.
Thank you again, and I will share the final proc once I am done writing it.
-
Here is a prime example of the problem I am having with my current code. This is the part where the IF statement has determined that an account has a Specific Overdraft Account available to it. I am so close it's not even funny, but look at this snipit of code, and you will see the results sets below (along with my input parameters). My Transaction tbl shows the correct balances (except for the withdrawal amount which I need to store as a negative - since it's a withdrawal), but my Account tbl for the life of me will not properly translate my value. I keep getting -$1.00 instead of the -$11.00 that should be shown. Account # 100070 has $500.00 in it's account, with a Specific Overdraft Account (Account #100069) which has $6000.00 in it. For using the Specific Overdraft Account - this project has me charge them a $10.00 fee, so when the account goes below $0.00, that kicks in. Now - everything except for my final value in the Account tbl is fine. It's driving me a bit crazy. Thanks to you all for any and all help!
-- Process for withdrawal from Accounts with TransactionTypes: Withdrawal(Type 3), Checkcard Purchases (Type 6),
-- and Check (Type 7) with Specific Overdraft Accounts.
DECLARE @CalcBalance1 MONEY = (SELECT CurrentBalance FROM Account WHERE AccountID = @AccountID)
DECLARE @OverDraftAccountID1 INT = (SELECT OverDraftAccountID FROM Account WHERE AccountID = @AccountID)
DECLARE @ODBalance MONEY = (SELECT CurrentBalance FROM Account WHERE AccountID = @OverDraftAccountID1)
DECLARE @SODPenalty MONEY = -10.00
SET @CalcBalance1 = @CalcBalance1 - @TransactionAmount
IF @CalcBalance1 < 0.00 AND @OverDraftAccountID1 IS NOT NULL
BEGIN
INSERT INTO [dbo].[Transactions] ([AccountID],[TransactionTypeID],[CustomerID],[TransactionAmount],[NewBalance])
VALUES (@AccountID,@TransactionTypeID,@CustomerID,@TransactionAmount,@SODPenalty + @CalcBalance1)
UPDATE [dbo].[Account]
SET CurrentBalance = @SODPenalty + @CalcBalance1
WHERE AccountID = @AccountID
INSERT INTO [dbo].[Transactions] ([AccountID],[TransactionTypeID],[CustomerID],[TransactionAmount],[NewBalance])
VALUES (@OverDraftAccountID1,@TransactionTypeID,@CustomerID,@CalcBalance1,@ODBalance + @CalcBalance1)
UPDATE [dbo].[Account]
SET CurrentBalance = @ODBalance + @CalcBalance1
WHERE AccountID = @OverDraftAccountID1
END
Input Parameters
Transaction tbl
Account tbl
Again, thank you all for any and all assistance, and my apologies on the lack of proper indenting. 😉
-
DECLARE @CalcBalance1 MONEY = (SELECT CurrentBalance FROM Account WHERE AccountID = @AccountID)
DECLARE @OverDraftAccountID1 INT = (SELECT OverDraftAccountID FROM Account WHERE AccountID = @AccountID)
DECLARE @ODBalance MONEY = (SELECT CurrentBalance FROM Account WHERE AccountID = @OverDraftAccountID1)
DECLARE @SODPenalty MONEY = -10.00
SET @CalcBalance1 = @CalcBalance1 - @TransactionAmount
IF @CalcBalance1 < 0.00 AND @OverDraftAccountID1 IS NOT NULL
BEGIN
INSERT INTO [dbo].[Transactions] ([AccountID],[TransactionTypeID],[CustomerID],[TransactionAmount],[NewBalance])
VALUES (@AccountID,@TransactionTypeID,@CustomerID,@TransactionAmount,@SODPenalty + @CalcBalance1)
UPDATE [dbo].[Account]
SET CurrentBalance = @SODPenalty + @CalcBalance1
WHERE AccountID = @AccountID
INSERT INTO [dbo].[Transactions] ([AccountID],[TransactionTypeID],[CustomerID],[TransactionAmount],[NewBalance])
VALUES (@OverDraftAccountID1,@TransactionTypeID,@CustomerID,@CalcBalance1,@ODBalance + @CalcBalance1)
UPDATE [dbo].[Account]
SET CurrentBalance = @ODBalance + @CalcBalance1
WHERE AccountID = @OverDraftAccountID1
END
Why are both getting hit with the @CalcBalance1 price? The OD should only be hit with the leftover debt, which you don't compute after applying the majority of the fee to the @AccountID.
Also, Rich, if you take a look at the first link in my signature, it'll help you setup the sample DDL and data that will allow us to help you easier. Consumable test structures are always better. 🙂
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Hi Craig -
CalcBalance1 is the leftover debt. It's also the new balance for the originating account. I guess I have it wrong. I thought they would be one in the same (top entry for the originating account and bottom entry for the specific overdraft account - both are the same, I thought).
My apologies on not knowing a bit more about forum etiquette before posting, and I will make sure to format accordingly next time!
So - are you saying that the CalcBalance1 is not the same for both the main account and my overdraft account? Sorry - my brain is fried, so forgive me if this is coming off like 2nd grade math.
:crazy:
-
Rich Yarger (2/6/2011)
Hi Craig -CalcBalance1 is the leftover debt. It's also the new balance for the originating account. I guess I have it wrong. I thought they would be one in the same (top entry for the originating account and bottom entry for the specific overdraft account - both are the same, I thought).
Um, 'ish. Your scenario should be taking (from my understanding) $500 from the main account, and $11 from the Overdraft account. The extra $1, and $10 fee.
So, you declare CalcBalance and set it to the current balance in the main account. (500) (check)
You then set it to itself - the transaction value. (-1) (check)
You then check the CalcBalance1 for being less than 0 and enter an If structure (check)
You then insert two transactions.
First transaction Is for the CalcBalance1 and the SODPenalty ( should be -11).
You then Update Account to -11. (this is where you're failing in theory. Thus, I'd like to see sample data/ddl).
Without editing CalcBalance1 you then go to update the OD account.
You insert a value of -1 + the OD Account's balance to the transactions
Then you update the OD Account to have a balance of itself - the calc balance.
So, I've been hit with an OD charge in an account that obviously can't handle it, and I've paid the overdrafted portion of the charge... twice.
I'd dump my bank very quickly if this happened.
So - are you saying that the CalcBalance1 is not the same for both the main account and my overdraft account? Sorry - my brain is fried, so forgive me if this is coming off like 2nd grade math.
That's precisely what I'm saying.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Yeah - sorry about that Craig. That's what I get for posting after nearly 24 straight hours of coding. Kind of like drunk dialing - just don't do it. :w00t:
I actually slept for a change and woke up with fresh eyes to see my mistake. Needless to say, I thank you, and was happy to see that I was in line with your advice here.
I need to finish this soon. But it has been fun getting there.
Thanks again, and see you in hopefully - better thought out, thread!
😉
Rich
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply