quote:

---

Originally posted by IleanaAlvare

Hi,

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.
My view should looks like:
ShipperID, Company, Phone, UnshippedOrders

Any help please????
Thanks

---

SELECT
	c.Company,
	c.Phone,
	o.ShipperId,
	COUNT(*) AS UnshippedOrders
FROM
	Customer c
	LEFT JOIN [Order] o ON o.CustomerId = c.CustomerId
WHERE
	ShippedDate IS NULL OR ShippedDate > GETDATE()
GROUP BY
	c.Company,
	c.Phone,
	o.ShipperId;

SELECT
	s.Company,
	s.Phone,
	s.ShipperId,
	COUNT(*) AS UnshippedOrders
FROM
	Shipper s
	LEFT JOIN [Order] o ON 
		o.ShipperId = s.ShipperId
		AND (o.ShippedDate IS NULL OR o.ShippedDAte > GETDATE())
GROUP BY
	s.Company,
	s.Phone,
	s.ShipperId