Write a SINGLE query using the customers and orders tables that does the followi

Question

Write a SINGLE query using the customers and orders tables that does the following: Contains columns with names Last Name, First Name, Order Number, Order Date/Time, Estimated Ship Date, Payment Type, and Amount. The values to go in these columns are explained more in the following bullets. Only returns order records that are less than 90 days old (from whenever query run) Displays the order datetime as yyyy-mm-dd and time in military (24 hour) time format Displays the customer name in all lower case. Make sure to trim off any extra spaces from the start and end of the names Displays the estimated ship date as 30 days from the order date. Only display date, not time. Payment type should display 'Credit Card' if type = 2, and '1 year Loan' if type = 1 Amount should = amount field value if type = 1, but for type = 2, should equal monthly payment given in the amount field, 12 monthly payments, and annual interest rate of 12% (compounded monthly): P = Cr(1+r)^N/(1+r)^N - 1 where C = amount, r = monthly decimal interest rate (=12/1200 = 0.01), and N=# of payments (12)

Explanation / Answer

SELECT LOWER(TRIM(LAST_NAME)), LOWER(TRIM(FIRST_NAME)), ORDER_NUMBER, FORMAT(ORDER_DATE,'YYYY-MM-DD'), ORDER_TIME,
           DATEADD(day,30,FORMAT(ORDER_DATE,'YYYY-MM-DD')),
           CASE WHEN PAYMENT_TYPE = '1' THEN 'CREDIT CARD' ELSE 'LOAN' END AS PAYMENT_TYPE,
           CASE WHEN PAYMENT_TYPE = '1' THEN AMOUNT ELSE AMOUNT*0.01*(1.01^12)/((1.01^12)-1) END AS AMOUNT

WHERE DATEDIFF(FORMAT(ORDER_DATE,'YYYY-MM-DD'),FORMAT(Now(),'YYYY-MM-DD')) <= 90

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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