Test your SQL Server savvy in this month's Reader Challenge. Submit your solution in an email message to challenge@ sqlmag.com by April 12. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Problem:
Siva is a database developer for a company that sells products online. The company stores its product inventory and order-processing data in a SQL Server 2000 database. For the purpose of the problem, use the tables in Listing 1 for the order-processing data.
The Orders table contains the details of each customer's online order form. The OrderDetails table contains the line items for each order. The status of the order is tracked at both the order and order detail levels, although the status values are different from table to table. Listing 2 shows some sample data for the tables.
Orders are created with at least one line item in a single transaction. The status change for a particular order happens in the following sequence: Orders are initially created with a "Processing" status. Once all of the line items in an order have been processed, the status changes to "Shipped." If the order is cancelled, the status is set to "Cancelled." A batch processes the OrderDetails entries, then updates the status of each line item. At the end of the batch process, Siva wants to update the Orders table and mark all orders that have been completely processed.
Help Siva complete the following tasks:
- Write an UPDATE statement that can change the order status according to the status of the line items.
- Optimize the schema by adding any additional indexes to help with the processing of the UPDATE statement or queries that look at orders of a particular status.
