Get Customer Records By Their Historic Sales Order

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get Customer Records By Their Historic Sales Order

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2011-04-18 : 19:01:54

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 - 2011-04-18 : 19:22:32

[code]
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)
[/code]

fawadafr
Starting Member

47 Posts

Posted - 2011-04-19 : 11:10:00

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

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-19 : 17:08:22

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/

fawadafr
Starting Member

47 Posts

Posted - 2011-04-26 : 19:01:23

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

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-27 : 02:03:45

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources