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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a View with a new column-URGENT

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,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

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,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

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 table
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;
Go to Top of Page

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 shipperID
Shipper ID = 1 has 2 orderId in Order Table that has been shipped and one with ShippedDate = NULL
Shipper 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 UnshippedOrders
1 ABC 111-222-3542 1
2 DCW 222-412-5555 0
How Can I get this?

thanks a bunch for your help
Go to Top of Page

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 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
Go to Top of Page

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 shipperID
Shipper ID = 1 has 2 orderId in Order Table that has been shipped and one with ShippedDate = NULL
Shipper 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 UnshippedOrders
1 ABC 111-222-3542 1
2 DCW 222-412-5555 0

Thanks again
Ileana
Go to Top of Page
   

- Advertisement -