# 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?

SQL.docx
###### Who is Participating?

x
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.

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(*)
Author 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
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

Experts Exchange Solution brought to you by