What is SQL for 2nd payment installment of every lease?

Hi All,

I am kind of circling with this issue.I have a scenario where leases have a number of scheduled payments called “installments”. The total number of installments on a lease is referred to as the “term” of the lease. Installments are typically scheduled on a monthly basis. Hence the unit of a lease term is in months. Smart Pay has leases on 11, 13, 15, 24 month terms.

Imagine a small database with the following tables, Below format isn't formatted. I have attached in Table format of data in Word file as attachment

leases
id      funding_date      status      amount      term
523433      2018-01-01      active      700      13
243434      2017-06-15      delinquent      450      13
434344      2017-09-13      closed      500      13

installments
id      lease_id      amount      due_date      status
24443      523433      53.85      2018-01-01      paid
43434      523433      53.85      2018-02-01      paid
65653      523433      53.85      2018-03-01      pending


-  What query will give the second installment of every lease?
-  What query will calculate the amount outstanding of every “active” lease?
            Amount Outstanding = Lease Amount - Paid Installment Amounts

-  What query will find all leases that are “delinquent” with a total number of installments that is less than the leases’ term?

Appreciate your inputs..many thanks
SQL.docx
Chandra SekharAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
What query will give the second installment of every lease? (beware...air code ahead)

WITH cteLeaseInstallment AS
(
SELECT L.id AS LeaseID,
   I.id AS InstallmentID,
   I.amount,
   I.due_date,
   I.status,
   ROW_NUMER() OVER(PARTITION BY L.id ORDER BY I.due_date) AS InstallmentNumber
FROM leases AS L
INNER JOIN installments AS I
   ON L.id = I.lease_id
)
SELECT *
FROM cteLeaseInstallment
WHERE InstallmentNumber = 2;

What query will calculate the amount outstanding of every “active” lease?

SELECT L.id AS LeaseID,
   L.amount - SUM(I.amount) AS AmountOutstanding
FROM leases AS L
INNER JOIN installments AS I
   ON L.id = I.lease_id
   AND status = 'paid'
WHERE L.status = 'active'
GROUP BY L.id

What query will find all leases that are “delinquent” with a total number of installments that is less than the leases’ term?

SELECT L.id AS LeaseID,
   L.amount - SUM(I.amount) AS AmountOutstanding
FROM leases AS L
INNER JOIN installments AS I
   ON L.id = I.lease_id
   AND status = 'paid'
WHERE L.status = 'delinquent'
GROUP BY L.id
HAVING L.term > COUNT(*)
0
Chandra SekharAuthor Commented:
Thank You Brian, really appreciate your help. I am trying to understand the ROW_NUMBER part,which is little confusing for me. I really appreciate your inputs please
0
Brian CroweDatabase AdministratorCommented:
The ROW_NUMBER (Sorry for the misspelling in the original response) function provides a simple method to order and select the second installment.  The assumption we are making in this case is that the due_date is an appropriate field with which to determine the installment number.

If you run just the cte query (the code inside the parentheses) it may become more obvious what is going on.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chandra SekharAuthor Commented:
I appreciate your help and solution. This looks great. I would like to connect and have quick chat for your help, look forward to your response
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
joins

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.