Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

possibly a function with existence check?

Posted on 2011-09-11
23
Medium Priority
?
194 Views
Last Modified: 2012-05-12
it's a lending database.  Upon original insertion, or loan submission, many checks are performed to determine whether it is actually a lendable application.  I am creating a new check, be it just a procedural check, or a function, it needs to be nothing but fast (and accurate, of course).

We've got two tables, LoanTable and CustomerTable, joined to eachother on CustomerID, and we need to check for the combined RoutingNumber + AccountNumber values, in the following four conditions:

The submitted RoutingNumber + AccountNumber per SSN should not also be
 A., associated with a loan in collection status.
 B., associated with a loan in outstanding status.
 C., associated to a customer within last 45 days, with a denial code of 5.
 D., associated to a customer with current loan balance > 0.


A & B checks:
LoanTable JOIN CustomerTable ON CustomerID
  WHERE LoanTable.Status IN (B,C,R,N,O,G)  -- collection status

C check:
LoanApps for the same RoutingNumber + AccountNumber within the last 45 days (DateEntered), DCode = 5.

D check:
Calculate LoanBalance, then for any record in CustomerTable with the same RoutingNumber + AccountNumber,  
sum all associated records and check whether balance is > 0.



This is an example check for loan balance:
select      
 cast(l.LoanAmt as decimal) + cast(l.FinCharge as decimal) - SUM(cast(Coalesce(p.Pmt, 0) as decimal)) as Balance      
FROM      
 dbo.LoanAppl l (NOLOCK) LEFT JOIN dbo.Payment p (NOLOCK) ON l.ApplNo = p.ApplNo      
WHERE      
 l.status IN ('N', 'O', 'P', 'D', 'B', 'R', 'C', 'G', 'V')
 and l.IsOriginated = 1      
 AND l.ApplNo = 52078012
GROUP BY      
 l.LoanAmt,      
 l.FinCharge


terribly pressing, any input is hugely appreciated
0
Comment
Question by:dbaSQL
  • 17
  • 4
  • 2
23 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 300 total points
ID: 36519871
sounds more like a select on customer with exists/not exists checks against the other tables/conditions...

can you do it in one pr do you need to return the failure reason?
(and is that the first failure or something more complex which details all fail reasons?)

a procedure rather than a function is probably the better solution
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36520042
hi lowfat.  i think i could probably do it in one.  basically, it's just a matter of checking whether the same routingnumber/accountnumber is found to be associated with any of the 4 conditions i listed.  performance is key, and i wasn't sure how best to construct the proc.   what do you think?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36520291
hey lowfat, a customer can go into the database, and have several loan submissions over time.  for example, customerID A maybe originated back in 1995, and had a loan application in '95, 2000, 2005 and 2010.  (just playing here, picking a few dates)

each one of those new loan submissions makes a new entry in LoanTable.
the routingnumber and accountnumber is only in customertable, and the LoanTable is joined to customerTable on CustomerID

IF any of those four conditions exists, I would be denying the loan for the customer's SSN.
So, it is at the entry into the loantable that I have to check those four conditions, if exists.

Do you see what I mean?

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36521175
so what are you giving to the procedure ...

the ssn so whats the big deal with the routing code/account number?

or the routing code and account number .... implies that more than one customer can exist for an ssn ,,. so what does that mean?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 36521772
what sql server version are you using ? sql 2008?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36522370
using v2008

in this check, it's the routingnumber/account that we need to key on.  the ssn is also one of the checks being performed

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36522428
i think your point is valid, lowfat.  i'm going to check upstream and see why we are not using the SSN, rather than the combined routingnumber/accountnumber.  say a customer opens a new account, they're going to get a new accountnumber.  but, the ssn stays the same.  would seem to me like that would be the value to key on.  i will let you know what i find.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36524368
I have learned there have been applications submitted with different SSNs on the same routingnumber/accountnumber.  so the check needs to be keyed on routingnumber/accountnumber.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36526642
Any thoughts on getting A, B, C and D into one procedural check?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36526807
well, i have all three pieces, A/B, C and D.  but i'm not very comfortable with the flow between each.  I'm also not confident that D is as optimal as it could be.  I just figured I'd raiserror on the balance > 0, but again, I'm not certain on the best method of wrapping all three together, with the RAISERROR to control the output to the client.

your thoughts?
--A/B
DECLARE @Status TABLE (Status CHAR(1), PRIMARY KEY (Status))
INSERT @Status VALUES
('B'),('C'),('R'),('N'),('O'),('G');

IF EXISTS(SELECT 1 FROM dbo.LoanTable l1 JOIN dbo.CustomerTable c
	  ON l1.customerID = c.customerID
	WHERE c.RoutingNumber = @RoutingNumber
	AND c.AccountNumber = @AccountNumber
	AND EXISTS(SELECT 1 FROM @Status l2 WHERE l1.Status = l2.Status))
	BEGIN
RAISERROR('The given RoutingNumber/AccountNumber is in the system with collection status.',0,1)  
	  RETURN;
	END


--C
IF EXISTS(SELECT 1 FROM dbo.LoanTable l 
WHERE l.DateEntered >= DATEADD(day,-45,GETDATE())
AND DCode = 5
AND l.RoutingNumber = @RoutingNumber
AND l.AccountNumber = @AccountNumber)
BEGIN
	   RAISERROR('The given RoutingNumber/AccountNumber has a DCode = 5, within last 5 days.',0,1)  
	RETURN;
END	


--D
DECLARE @Status TABLE (Status CHAR(1), PRIMARY KEY (Status))
INSERT @Status VALUES
('N'),('O'),('P'),('D'),('B'),('R'),('C'),('G'),('V');

SELECT 1 FROM
(
SELECT CAST(la.LoanAmt AS DECIMAL) + CAST(la.Charge AS DECIMAL) - SUM(CAST(COALESCE(p.Pmt, 0) AS DECIMAL)) AS Balance
	FROM dbo.LoanTable la (NOLOCK) LEFT JOIN dbo.PaymentTable p (NOLOCK) 
	   ON la.Ano = p.ANo JOIN dbo.CustomerTable c
	     ON la.CustomerID = c.CustomerID      
	WHERE la.IsO = 1      
	AND EXISTS(SELECT 1 FROM @Status l WHERE l.Status = la.Status) 
	GROUP BY la.LoanAmt,la.Charge ) x
	HAVING SUM(x.Balance) > 0

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36532357
lowfat, or eugene?  any suggestions on the most appropriate way of wrapping this into a proc?  optimally?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1700 total points
ID: 36533483
--without going into logic , after long work day:
please check this one
create proc dbo.usp_yourproc  @RoutingNumber int,@AccountNumber varchar(100)
as
--A/B
IF EXISTS(SELECT 1 FROM dbo.LoanTable l1 JOIN dbo.CustomerTable c
	  ON l1.customerID = c.customerID
	WHERE c.RoutingNumber = @RoutingNumber
	AND c.AccountNumber = @AccountNumber
	AND l1.status in ('B','C','R','N','O','G'))
	BEGIN
             RAISERROR('The given RoutingNumber/AccountNumber is in the system with collection status.',0,1)  
	 
	END


--C
IF EXISTS(SELECT 1 FROM dbo.LoanTable l 
WHERE l.DateEntered >= DATEADD(day,-45,GETDATE())
AND DCode = 5
AND l.RoutingNumber = @RoutingNumber
AND l.AccountNumber = @AccountNumber)
BEGIN
	   RAISERROR('The given RoutingNumber/AccountNumber has a DCode = 5, within last 5 days.',0,1)  
	
END	


--D
IF EXISTS(
SELECT 1 FROM
(
SELECT CAST(la.LoanAmt AS DECIMAL) + CAST(la.Charge AS DECIMAL) - SUM(CAST(COALESCE(p.Pmt, 0) AS DECIMAL)) AS Balance
	FROM dbo.LoanTable la (NOLOCK) LEFT JOIN dbo.PaymentTable p (NOLOCK) 
	   ON la.Ano = p.ANo JOIN dbo.CustomerTable c
	     ON la.CustomerID = c.CustomerID      
	WHERE la.IsO = 1      
	 AND la.status in ('B','C','R','N','O','G')
	GROUP BY la.LoanAmt,la.Charge ) x
	HAVING SUM(x.Balance) > 0)
	begin 
	  select 'D'
	end
	
	go

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36536119
fairly simple, it seems.  thank you, eugene.  i can't test it out until a little later, but i will let you know
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36536238
let's say i just want to return 0/1, pass or fail, if any of the checks come back w/results.  
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 36537935
for example


begin
  select 1 [A/B]
...
end

--
in case all criterias may work at same run and you wish to have combined result
like you can use some temp table...

status |  criteria
1              A/B
0              C
1             D
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 36539573
you still can use  
Begin
RETURN(1)..

End
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36539605
if any check fails, we will return 0, and be done with it.
if the checks collectively succeed, we will return 1, and be done.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36539739
couldn't i just do this:

BEGIN
   RAISERROR('The given RoutingNumber/AccountNumber is in the system with collection status.',0,1)  
   RETURN (0)
END

yeah?

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36539974
naah, i think i'll do it without the RAISERROR

Begin
RETURN(1)..

End


eugene, do you feel that either approach is better than the other?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36539984
>>if any check fails, we will return 0, and be done with it.
if the checks collectively succeed, we will return 1, and be done.

I should clarify.  By 'fail', i did not mean a functional problem with the sql, I just meant if it found data.  IF EXISTS came back with anything, that is a bad check.  Or a 'failure', per se.  0

If it comes back with nothing, the check is clean, or a success.  Or 1.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36568912
I am so sorry for having left this open.  Eugene, lowfat, please forgive.  for my output, I ended up using the SELECT suggested here, eugene:  ID:36537935
thank you both very much for your time.  
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 36568930
>>a procedure rather than a function is probably the better solution
You are right, lowfat, the procedure was fine.  Thank you, Eugene, for your suggestion.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36593482
Hey lowfat, or eugene, i put that procedure together the other day, but it really is not performing as well as is needed.  I have reviewed the indices/constraints, and that is good.  There was a table scan that i managed to change to a clustered index scan.... couldn't get to the seek.  Would either of you have a moment to take a look at the logic?  i can open another inquiry, if necessary.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question