Customer Ỹ CustomerID FirstName LastName Phone Address Car CarlD Make Model Colour PlateNumber Job JobID CustomerID CarlD JobStartTime Task TaskID JobID Description JobCompleted JobID JobCompleted Time Payment PaymentID JobID Amount Timestamp TaskPart TaskID PartID Count TaskStaff TaskID StaffID Minutes Part PartID PartName PartPrice Staff StaffID FirstName LastName Address Phone
Task 12B ● Using Task 12A, create a view that shows the outstanding balance per customer. • You can simply sum the outstanding balance of all the jobs that belong to that customer and max the last payment date. • Customers who have no jobs must not appear. • The result should have (Customerld, FirstName, LastName, BalanceOutstanding, NumberOfPayments, LastPayment Timestamp, LastPaymentDate). ● LastPaymentDate is the human readable format of LastPayment Timestamp. . This should be in the format of 'dd-mm-yyyy'.
Task 12C ● Assuming that today's date was 10-10-2016, (Unix Time: 1476057600), create a view that shows the customers who have not paid for over 45 days AND have a BalanceOutstanding greater than zero.
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!