SQL HELP

It is possible to receive partial shipments. For example, lets say that MountainDesign ordered a quantity of 12 for a product. It is possible to receive 3 of those products on one receiver, 2 of the products on another receiver, and 7 of the products on a final receiver. That means there will be 3 different rows in the receiver table for that particular product on that given purchase order with a given date needed. You must sum the quantity received in the receiver table by POLineID to get an accurate amount of the number of products received in total for a given product on a given purchase order.

There are both open and closed purchase orders in this database. An open purchase order is an order that has not yet had all its required products fully received. A closed purchase order is an order that has had all its products fully received. A purchase order is considered closed even if there have been overshipments (too many products received) of products.

An overshipment occurs when more products were received than were ordered. It is possible to have overshipments in this database. There is no flag field to show when an overshipment has occurred.

A purchase order where none of the products have been received is considered an open purchase order.

Here is a summary of potential purchase order status conditions:

o Open: Products have not been received at all or have been partially received.

o Closed: Products have been fully received or have been overshipped.

Here is a summary of potential product received conditions on a purchase order:

o Not received: None of a particular product on a purchase order has been received.

o Complete: All of a particular product on a purchase order has been fully received.

o Partially Received: Some of a particular product on a purchase order has been received (also referred to as partial shipment in the assignment).

o Over Received (also referred to as Over Shipment or Received Too Many in the assignment): More of a particular product has been received than was ordered.

Generating the same columns as that produced for question #4, make a result table listing all the open purchase orders. An open order is any purchase order in the database that has not yet been completely received. The purchase order may have some purchase order lines that have been completely received, others than have partial shipments, or even some that have not been received at all. Some purchase orders have no purchase orderlines that have been received. All of those purchase orders would be considered open. Here is the complete result table: