SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a View with a new column-URGENT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IleanaAlvare
Starting Member

Canada
7 Posts

Posted - 01/31/2014 :  09:02:11  Show Profile  Reply with Quote
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

Edited by - IleanaAlvare on 01/31/2014 11:46:00

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/31/2014 :  12:16:09  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 01/31/2014 :  12:29:20  Show Profile  Reply with Quote
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

Edited by - IleanaAlvare on 01/31/2014 12:32:43
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/31/2014 :  14:40:51  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 01/31/2014 :  20:31:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000