Write a soL statement that will pull balance and last payment amount by account

Question

Write a soL statement that will pull balance and last payment amount by account for the previous 7 days of data. Do not hard-code your dates 2 Write a query to pull the total payments and average payment amount received in January 2001 3 Write a query to pull each account's most recent balance. Add an additional column that marks the account as Paid Off is balance is zero 4Using temp tables, build a SQL statement that will show the final balance of each account, by month 5 Write a SQL statement to show how much principal each customer paid in their last payment. If data is unavailable, simply return O 7For the purpose of this example, "Today" is 1/15 2001 Table Name: dbo.Balances Account Number AsOfDate Table Name Account Number Last Paid Last Paid Amount AsOfDate 12/31/00 12/30/16 2/31/00 5000.00 1/1/01 12/30/16 /2/01 2/30/16 /3/01 2/30/16 1/4/01 100.00 1100.00 1100.00 1100.00 1100.00 1100.00 1100.00 1100.00 1100.00 1100.00 100.00 4200.00 1/6/01 1/4/01 1/7/01 4200.00 4200.00 4200.00 /10/014200.00 1/11/01 4200.00 1/12/01 4200.00 /13/01 4200.00 1/7/01 1/4/01 1/8/01 1/9/01 1/10/01 1/11/01 1/12/01 1/13/01 1/14/01 12/31/00 12/15/00 1/4/01 1/4/01 1/4/01 1/4/01 114/01 4200 2/31/00 10000.00 /1/0110000.00 12/15/00 2/01 12/15/00 1/3/01 12/15/00 /4/01 10000.00 /5/0110000.00 1/6/01 10000.00 7/01 10000.00 1/8/01 10000.00 /9/0110000.00 /10/01 10000.00 1/11/01 10000.00 1/12/01 10000.00 1/13/01 10000.00 1/14/01 10000.00 4/01 12/15/00 1/5/01 12/15/00 1/6/0 12/15/00 /7/0112/15/00 /8/0112/15/00 /9/0112/15/00 1/10/01 12/15/00 /11/01 1/11/01 112/0 1/11/01 /13/011/11/01 14/011/11/0 37

Explanation / Answer

Answer:-

Sql>SELECT ISNULL( (SELECT t1.AccountNumber

FROM Balances t1

INNER JOIN (SELECT AccountNumber, MAX(LastPaid) AS MaxDate, LastPaidAmount

FROM Payments

GROUP BY AccountNumber) t2 ON t2. AccountNumber = t1.AccountNumber

), 0);

In above, first I set a return value 0 if no records found.

If any records are exists then first it select the AccountNumber from Balance table and INNER JOIN with sub query on Payments table that contains the last paid information of the coustomer. And in last, those customers who have same AccountNumber will show their last paided principle.

I have not any database that same as questions. I just think the process and answer. SO please do not rate it bad.

Thank you.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.