| Author |
 Topic  |
|
|
fawadafr
Starting Member
USA
47 Posts |
 Posted - 04/18/2011 : 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
149 Posts |
Posted - 04/18/2011 : 19:22:32
|
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)
|
 |
|
|
fawadafr
Starting Member
USA
47 Posts |
Posted - 04/19/2011 : 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
Czech Republic
518 Posts |
Posted - 04/19/2011 : 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
USA
47 Posts |
Posted - 04/26/2011 : 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
Czech Republic
518 Posts |
Posted - 04/27/2011 : 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/ |
|
|
| |
Topic |
|
|
|
Get Customer Records By Their Historic Sales Order
