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

We have to match the date string with the potential last date of every month. February has 28 as its last date, while April, June, September and November have 30 as their last date, while all the other months have 31 as their last date. So we compare the date string appropriately. And we need to prepare the report for each distinct account number.

ans:

SELECT distinct(Account Number), balance

FROM dbo.Balances

WHERE AsOfDate LIKE '%31%' OR AsOfDate LIKE '2/28%' OR AsOfDate LIKE '4/30%' OR AsOfDate LIKE '6/30%' OR AsOfDate LIKE '9/30%' OR AsOfDate LIKE '11/30%'   

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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