Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
IleanaAlvare
Starting Member
7 Posts |
Posted - 2014-01-31 : 09:02:11
|
Hi,I'm using SQL Server 2008 to solve the following exercise.I have three Tables:Table CUSTOMER with columns: CustomerID,Company,ContactName,PhoneTable ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerIDTable Shipper with columns:ShipperID,Company,PhoneI 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, UnshippedOrdersAny help please???? Thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-01-31 : 12:16:09
|
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,PhoneTable ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerIDTable Shipper with columns:ShipperID,Company,PhoneI 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, UnshippedOrdersAny help please???? Thanks
You have Phone and Company in both Shipper and Customer tables. Which phone number and company are you trying to get? The following assumes you want to get those from Customer tableSELECT c.Company, c.Phone, o.ShipperId, COUNT(*) AS UnshippedOrdersFROM Customer c LEFT JOIN [Order] o ON o.CustomerId = c.CustomerIdWHERE ShippedDate IS NULL OR ShippedDate > GETDATE()GROUP BY c.Company, c.Phone, o.ShipperId; |
|
|
IleanaAlvare
Starting Member
7 Posts |
Posted - 2014-01-31 : 12:29:20
|
Hi James,I'm trying to get Company and Phone from Shipper Table.I need the new column UnshippedOrders to have a total of unshipped orders for each Shipper id, for example let say:Shipper Table only has 2 shipperIDShipper ID = 1 has 2 orderId in Order Table that has been shipped and one with ShippedDate = NULLShipper ID = 2 has 2 oderID in Order Table that has been shipped and NO order with ShippedDate = NULL.based on this my results should look like:ShipperID Company Phone UnshippedOrders1 ABC 111-222-3542 12 DCW 222-412-5555 0How Can I get this?thanks a bunch for your help |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-01-31 : 14:40:51
|
Can you try this to see if it gives you what you are looking for?SELECT s.Company, s.Phone, s.ShipperId, COUNT(*) AS UnshippedOrdersFROM 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 |
|
|
IleanaAlvare
Starting Member
7 Posts |
Posted - 2014-01-31 : 20:31:26
|
Hi James,First of all, I really appreciate your help on this.Your last query is partially working, I said partially because using again the same example as below, for ShipperID = 2 I'm getting UnshippedOrders=1 instead of 0. How can I solve this?DATE EXAMPLE:Shipper Table only has 2 shipperIDShipper ID = 1 has 2 orderId in Order Table that has been shipped and one with ShippedDate = NULLShipper ID = 2 has 2 oderID in Order Table that has been shipped and NO order with ShippedDate = NULL.based on this my results should look like:ShipperID Company Phone UnshippedOrders1 ABC 111-222-3542 12 DCW 222-412-5555 0Thanks againIleana |
|
|
|
|
|
|
|