Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get Customer Records By Their Historic Sales Order
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fawadafr
Starting Member

USA
47 Posts

Posted - 04/18/2011 :  19:01:54  Show Profile  Reply with Quote
Hello all:

I am trying to get list of all the customers who have purchased product 'AAA123' but have not purchased products 'ZZZ123' or 'YYY123' or 'XXX123' from us.

I tried first reading every line item in the OrderDetails table but I don't think I am getting the right data.

I was hopping someone could provide some help on this.

Thank you in advance,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

singularity
Posting Yak Master

153 Posts

Posted - 04/18/2011 :  19:22:32  Show Profile  Reply with Quote

select distinct customer
from OrderDetails a
where a.product 'AAA123' and
      not exists (select *
                  from OrderDetails b
                  where b.product in ('ZZZ123','YYY123','XXX123') and
                        a.customer = b.customer)
Go to Top of Page

fawadafr
Starting Member

USA
47 Posts

Posted - 04/19/2011 :  11:10:00  Show Profile  Reply with Quote
Thank you very much for your help @Singularity. I was able to apply your logic in my code and get the report completed. However, I had to take an extra step in Excel to remove duplicate customer data from the report. The duplicate customer data is as a result of many orders and order details (line items). I even tried using GROUP BY to encompass this but no luck so far. Please review the code bellow and let provide your tips if I need to add a filter:


SELECT
	  c.CustomerID
	 ,c.FirstName
	 ,c.LastName
	 ,c.BillingAddress1             AS	[Address]
	 ,c.City                        AS	[City]
	 ,c.State                       AS	[State]
	 ,c.PostalCode                  AS      [Zip Code]
	 ,c.Country                     AS	[Country]
	 ,ISNULL(c.PhoneNumber, '')	AS	[Phone]
	 ,c.Custom_Field_Custom4	AS	[Primary System]
	 ,c.Custom_Field_Custom5	AS	[Secondary System]
	 ,c.Custom_Field_Custom3	AS	[# implant]
	 
	 ,(SELECT CONVERT(VARCHAR(11), MIN(m.OrderDate), 101) FROM Orders AS m WHERE m.CustomerID = c.CustomerID) AS [First OrderDate]
	 ,(SELECT CONVERT(VARCHAR(11), MAX(m.OrderDate), 101) FROM Orders AS m WHERE m.CustomerID = c.CustomerID) AS [Last OrderDate]
	 
FROM OrderDetails AS od
	 INNER JOIN Orders AS o
	 ON od.OrderID = o.OrderID
	 INNER JOIN Customers AS c
	 ON o.CustomerID = c.CustomerID

WHERE od.ProductCode LIKE '%57%' AND 
	  ISNUMERIC(LEFT(od.ProductCode, 6)) = 1 AND
	  od.ProductPrice > 100 AND 
	  o.OrderStatus <> 'Cancelled' AND 
	  NOT EXISTS (SELECT *
				 FROM OrderDetails AS od1
				 INNER JOIN Orders AS o1
				 ON od1.OrderID = o1.OrderID
				 WHERE od1.ProductCode IN ('SD5.4','BST','CST') AND o1.OrderStatus <> 'Cancelled' AND o1.CustomerID = o.CustomerID)

GROUP BY
	  c.CustomerID
	 ,c.FirstName
	 ,c.LastName
	 ,c.BillingAddress1
	 ,c.City
	 ,c.State
	 ,c.PostalCode
	 ,c.Country
	 ,c.PhoneNumber
	 ,c.Custom_Field_Custom4
	 ,c.Custom_Field_Custom5
	 ,c.Custom_Field_Custom3
	 ,od.ProductCode

ORDER BY
	  c.CustomerID
	 ,od.ProductCode ASC


--
Fawad Rashidi
Web Developer
www.fawadafr.com
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/19/2011 :  17:08:22  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
If I got constraints right, you did not need to group by product code.

select
	  c.CustomerID
	 ,c.FirstName
	 ,c.LastName
	 ,c.BillingAddress1
	 ,c.City
	 ,c.State
	 ,c.PostalCode
	 ,c.Country
	 ,c.PhoneNumber
	 ,c.Custom_Field_Custom4
	 ,c.Custom_Field_Custom5
	 ,c.Custom_Field_Custom3
	 ,t.[First OrderDate]
	 ,t.[Last OrderDate]
from (
SELECT
	  o.CustomerID
	 , CONVERT(VARCHAR(11), MIN(m.OrderDate), 101) AS [First OrderDate]
	 , CONVERT(VARCHAR(11), MAX(m.OrderDate), 101) AS [Last OrderDate]
	 
FROM OrderDetails AS od
	 INNER JOIN Orders AS o
	 ON od.OrderID = o.OrderID
WHERE od.ProductCode LIKE '%57%' AND 
	  ISNUMERIC(LEFT(od.ProductCode, 6)) = 1 AND
	  od.ProductPrice > 100 AND 
	  o.OrderStatus <> 'Cancelled' AND 
	  NOT EXISTS (SELECT *
				 FROM OrderDetails AS od1
				 INNER JOIN Orders AS o1
				 ON od1.OrderID = o1.OrderID
				 WHERE od1.ProductCode IN ('SD5.4','BST','CST') AND o1.OrderStatus <> 'Cancelled' AND o1.CustomerID = o.CustomerID)
group by o.customerID
) t
JOIN Customers AS c ON t.CustomerID = c.CustomerID
ORDER BY
	  c.CustomerID


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

fawadafr
Starting Member

USA
47 Posts

Posted - 04/26/2011 :  19:01:23  Show Profile  Reply with Quote
Yes, indeed the constraints were correct. The only thing I fixed was:


, CONVERT(VARCHAR(11), MIN(o.OrderDate), 101) AS [First OrderDate]
, CONVERT(VARCHAR(11), MAX(o.OrderDate), 101) AS [Last OrderDate]


Thank you very much for your help!

--
Fawad Rashidi
Web Developer
www.fawadafr.com
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/27/2011 :  02:03:45  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by fawadafr

Yes, indeed the constraints were correct. The only thing I fixed was:


, CONVERT(VARCHAR(11), MIN(o.OrderDate), 101) AS [First OrderDate]
, CONVERT(VARCHAR(11), MAX(o.OrderDate), 101) AS [Last OrderDate]


Thank you very much for your help!

--
Fawad Rashidi
Web Developer
www.fawadafr.com

You are wellcome

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next 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.12 seconds. Powered By: Snitz Forums 2000