CREATE TABLE Orders (
OrderID int identity(1,1) not null primary key, 
DateOrdered, 
CustomerNumber)

CREATE TABLE OrderDetails(
DetailsID int identity(1,1) not null primary key, 
OrderID int references Orders(OrderID)
ProductOrdered varchar(30) 
ProductPrice  decimal(19,4)
Shipped char(1) default('N') )

CREATE VIEW VW_ORDERS AS
SELECT 
Orders.OrderID,
Orders.DateOrdered,
Orders.CustomerNumber
CASE 
  WHEN DETAILS.SHIPPEDCOUNT = DETAILS.TOTALCOUNT
  THEN 'Shipped'
  WHEN DETAILS.SHIPPEDCOUNT =0
  THEN 'Not Shipped'
  WHEN DETAILS.SHIPPEDCOUNT < DETAILS.TOTALCOUNT
  THEN 'Partial Shipment'
END As Status
FROM VW_ORDERS
LEFT OUTER JOIN 
(SELECT OrderID,
SUM(CASE WHEN Shipped = 'Y' THEN 1 ELSE 0 END) AS SHIPPEDCOUNT,
COUNT(DetailsID)  AS TOTALCOUNT
FROM OrderDetails
GROUP BY OrderID) DETAILS
ON OrderDetails.OrderId = DETAILS.OrderID