A pizza company is taking orders from customers, and each pizza ordered is added to their...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
A pizza company is taking orders from customers, and each pizza ordered is added to their database as a separate order. Each order has an associated status, "CREATED or SUBMITTED or DELIVERED". An order's Final Status is calculated based on status as follows: 1. When all orders for a customer have a status of DELIVERED, that customer's order has a Final Status of COMPLETED. 2. If a customer has some orders that are not DELIVERED and some orders that are DELIVERED, the Final Status is IN PROGRESS. 3. If all of a customer's orders are SUBMITTED, the Final Status is AWAITING PROGRESS. 4. Otherwise, the Final Status is AWAITING SUBMISSION. Write a query to report the customer_name and Final Status of each customer's order. Order the results by customer name. Table definitions and a data sample are given below. Schema Table: Customer_Order column name column type customer_name varchar2(50) order_id varchar2(10) status varchar2(50) Sample Data Tables Table: Customer_Order Customer_name order_id status John J1 DELIVERED John J2 DELIVERED David D1 SUBMITTED David D3 CREATED Smith S1 SUBMITTED KRISH K1 CREATED When all orders are in DELIVERED status then the Final Status is "COMPLETED". When one or more orders for a customer are DELIVERED and one or more orders are CREATED or SUBMITTED, then the Final Status is "IN PROGRESS". When one or more orders for a customer are SUBMITTED and none are DELIVERED, then the Final Status is "AWAITING PROGRESS". Otherwise, the Final Status is "AWAITING SUBMISSION". Order priority is DELIVERED, SUBMITTED, CREATED. The results are: customer_name Final Status David IN PROGRESS John COMPLETED KRISH AWAITING SUBMISSION Smith AWAITING PROGRESS A pizza company is taking orders from customers, and each pizza ordered is added to their database as a separate order. Each order has an associated status, "CREATED or SUBMITTED or DELIVERED". An order's Final Status is calculated based on status as follows: 1. When all orders for a customer have a status of DELIVERED, that customer's order has a Final Status of COMPLETED. 2. If a customer has some orders that are not DELIVERED and some orders that are DELIVERED, the Final Status is IN PROGRESS. 3. If all of a customer's orders are SUBMITTED, the Final Status is AWAITING PROGRESS. 4. Otherwise, the Final Status is AWAITING SUBMISSION. Write a query to report the customer_name and Final Status of each customer's order. Order the results by customer name. Table definitions and a data sample are given below. Schema Table: Customer_Order column name column type customer_name varchar2(50) order_id varchar2(10) status varchar2(50) Sample Data Tables Table: Customer_Order Customer_name order_id status John J1 DELIVERED John J2 DELIVERED David D1 SUBMITTED David D3 CREATED Smith S1 SUBMITTED KRISH K1 CREATED When all orders are in DELIVERED status then the Final Status is "COMPLETED". When one or more orders for a customer are DELIVERED and one or more orders are CREATED or SUBMITTED, then the Final Status is "IN PROGRESS". When one or more orders for a customer are SUBMITTED and none are DELIVERED, then the Final Status is "AWAITING PROGRESS". Otherwise, the Final Status is "AWAITING SUBMISSION". Order priority is DELIVERED, SUBMITTED, CREATED. The results are: customer_name Final Status David IN PROGRESS John COMPLETED KRISH AWAITING SUBMISSION Smith AWAITING PROGRESS
Expert Answer:
Answer rating: 100% (QA)
Below I have added the SQL query which uses case statements to provide solution for above problem st... View the full answer
Related Book For
Auditing A Practical Approach with Data Analytics
ISBN: 978-1119401742
1st edition
Authors: Raymond N. Johnson, Laura Davis Wiley, Robyn Moroney, Fiona Campbell, Jane Hamilton
Posted Date:
Students also viewed these accounting questions
-
A pizza delivery company promises to deliver all customer orders within one hour of receiving an order at one of its stores. Actual data for all pizza delivered during the past week showed that the...
-
An aerospace company has submitted bids on two separate federal government defense contracts. The company president believes that there is a 40% probability of winning the first contract. If they win...
-
A mail- order company has one department for taking customer orders and another for handling com-plaints. Currently, each department has a separate telephone number. Each has 7 telephone lines served...
-
Accounting for Contingencies and Provisions In accounting, contingencies and provisions play a crucial role in preparing financial statements and ensuring accurate representation of a company's...
-
A distributor of large appliances needs to determine the order quantities and reorder points for the various products it carries. The following data refer to a specific refrigerator in its product...
-
An expander operates adiabatically with nitrogen entering at T\ and P\ w ith a molar flow rate n. The exhaust pressure is 15. and the expander efficiency is IJ. Estimate the power output of the...
-
How to design a new or revise a current cost management system will depend on the characteristics of the company. Important factors of a company include the following items 1 through 7. For each of...
-
Oak Island Amusements Center provides the following data on the costs of maintenance and the number of visitors for the last three years: Number of Visitors per Year: Maintenance Costs (thousands)...
-
10- Ch 12 (i Saved Spicer Inc. showed the following alphabetized list of adjusted account balances at December 31, 2 Accounts Payable Accounts Receivable Accumulated depreciation, Equipment $ 26,460...
-
> A spring-mass system is a fundamental concept in physics and engineering that describes the dynamic behavior of a mass attached to a spring. This system is commonly used to model and analyze...
-
(d) Consider the directed graph shown in Figure Q2(d), "S" is the start state and "G1,G2, G3" are 3 goal states. When traversing the graph, one can move only in the direction indicated by the arrows....
-
TABLE OF CONTENTS 1. Introduction 2. Download Code and Setup 3. Problem 1: Battery Meter Simulation o 3.1. Overview o 3.2. batt update.c: Updating the Display with User Code o 3.3. Battery Meter...
-
Buffalo Company reported the following amounts in the stockholders' equity section of its December 31, 2019, balance sheet. Preferred stock, 10%, $100 par (10,000 shares authorized, 2,200 shares...
-
A rocket sled moves along a track such that its position is given by the function x(t) show below. The values of the constants are A = 2.00 m/s, B = 4.00 m/s. x(t) = At + Bt (a) What is the position...
-
How much a equity does a company with project with mean return of 5% of assets per year and the standard deviation of 5% of assets per year currently need to have in order to be 95% sure that its...
-
Provide an key outline based on the below: Consider an investment in an international venture. Be specific with your investment (product, service, etc.). Identify the advantages and disadvantages of...
-
Respond to the following questions: How well did my videos help you understand DMAIC methodology and the ability to transfer newly gained knowledge to real-life problem solving situations? Explain....
-
An annual report of The Campbell Soup Company reported on its income statement $2.4 million as equity in earnings of affiliates. Journalize the entry that Campbell would have made to record this...
-
If an auditor identifies an internal control weakness for an assertion, how does it affect the audit strategy? If the auditor identifies an internal control strength for an assertion, how does it...
-
The key documents involved in recording a purchase adjustment involve: a. A purchase return authorization, a shipping report, and a debit memo. b. A vendors invoice, a receiving report, and a credit...
-
Auditors of publicly traded companies are required to perform a(an) ________ for their clients. a. Compliance audit b. Integrated audit c. Internal audit d. Operational audit
-
Extraction is almost invariably a ternary mass transfer problem instead of binary because of partial miscibility of diluent and solvent. Typically, as solute is removed from diluent, solvent is less...
-
Both the Kremser and Colburn equations have special forms when \(\mathrm{mV} / \mathrm{L}=1.0\). The results of comparing these equations are Eqs. (16-33) and (16-36a), which relate HETP to...
-
How should the leaving chief executive be involved in the succession planning and transition process?
Study smarter with the SolutionInn App