Open with Google Docs

ANALYST PROJECT

NOTE: ALL DATA IS MOCK AND DOES NOT CONTAIN PII

Operational Accounting received a list of payments from a third party. Payment List.txt is the payment file and Account List.txt is the borrower's current balance by loan. Both files contain $($SSN$),$ Loan, and Amount fields.

Directions

Compare the Payment list to the Account List to find matching payments by SSN and Loan number.

For the payments identified

a$)$ Compare the Payment list Amt $($Amount$)$ against the Account list Bal $($Balance$).$

b$)$ Confirm by each loan if the payment will leave a balance, pay the loan in full $($PIF$),$ require a refund to the borrower, or require a refund to the payee.

NOTE: Refunds to the payee are payments that do not have a matching borrower$/$loan$($s$)$ on system.

This will also be a re$-$occurring request for files received in this format. Please create a template$/$repeatable process that could be used to do a comparison by an associate with little knowledge of the process.

SQL Project

SQL Tables

Table$1$ includes SSN$,$ Loan Number, Payment amount

Table$2$ includes SSN$,$ Loan Number, CurrentLoanBalance

A$.$ How would you

Write a query that would return results to show the sum of all payments made to a loan, and its current loan balance

B$.$ Write a query that would show SSNs$/$Loans$/$Current balance for loans that have not made a payment.

Page

$1$

$1$