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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get Customer Records By Their Historic Sales Order
 New Topic  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

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