possibly a function with existence check?
Posted on 2011-09-11
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
LoanApps for the same RoutingNumber + AccountNumber within the last 45 days (DateEntered), DCode = 5.
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:
cast(l.LoanAmt as decimal) + cast(l.FinCharge as decimal) - SUM(cast(Coalesce(p.Pmt, 0) as decimal)) as Balance
dbo.LoanAppl l (NOLOCK) LEFT JOIN dbo.Payment p (NOLOCK) ON l.ApplNo = p.ApplNo
l.status IN ('N', 'O', 'P', 'D', 'B', 'R', 'C', 'G', 'V')
and l.IsOriginated = 1
AND l.ApplNo = 52078012
terribly pressing, any input is hugely appreciated