Items Not Purchased

Last Post 10 Dec 2012 08:08 AM by shivanand_k. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
H654381
New Member
New Member

--
05 Dec 2012 10:51 AM
I have three tables.

1. Customers
2. Products
3. Customer Purchases

I can easily query to find the products that a customer purchases. I need to know the products they didn't purchase.

Example:

Customer Name
Product1 = 5 purchases
Product2 = 3 purchases
Product3 = 0 purchases

In essence, I querying for data that exists and data that doesn't exist. I have many customers and many products and many, many transactions. Business is good. :D

H654381
New Member
New Member

--
05 Dec 2012 01:07 PM
If anyone can help on this, I would really appreciate it. I've tried this many different ways and each time the result set isn't completely correct.
shivanand_k
New Member
New Member

--
10 Dec 2012 08:01 AM
Here is a script that you can use to build your query. I am using temp tables to represent your tables. replace those with your temp tables:

-- Temp Table represent Customers
create table #Customers (
CustomerID int,
CustomerName varchar(50)
)

insert into #Customers values (1,'John Doe'), (2,'Sam Dammy') , (3, 'Ram Rammy') , (4,'No Nosy')

-- Temp Table represent Products
create table #Products (
ProductID int,
ProductName varchar(50)
)

insert into #Products values (1, 'Product1') , (2,'Product2'), (3,'Product3')

-- Temp Table represent CustomerPurchase

create table #CustomerPurchase (
CustomerID int,
ProductID int,
Quantity numeric(5,0)
)

insert into #CustomerPurchase values (1,2,10.0), (2,3,5.0), (1,2,6.0) , (3,2,9.0), (2,2,4.0), (3,1,20.0), (1,2,4.0), (3,3,40.0) ;

-- Create a temp table to hold Product & customer combination (in real situation it can be a view)
create table #tmpProductCustomer (
CustomerID int,
ProductID int,
ProductName varchar(50)
)
insert into #tmpProductCustomer
select cp.CustomerID, p.ProductID,p.ProductName from
#Products p left join #CustomerPurchase cp on p.ProductID = cp.ProductID
union
select cp.CustomerID, p.ProductID,p.ProductName from
#Products p left join #CustomerPurchase cp on p.ProductID <> cp.ProductID


-- all combination
select a.CustomerID, c.CustomerName, a.ProductName , Sum(isnull(cp.Quantity,0)) as QuantityPurchased
from #tmpProductCustomer a
left join #CustomerPurchase cp on a.CustomerID = cp.CustomerID and a.ProductID = cp.ProductID
left join #Customers c on a.CustomerID = c.CustomerID
where a.CustomerID is not null
group by a.CustomerID, c.CustomerName, a.ProductName
Order by a.CustomerID, a.ProductName

--- Items not purchased
select a.CustomerID, c.CustomerName, a.ProductName , Sum(isnull(cp.Quantity,0)) as QuantityPurchased
from #tmpProductCustomer a
left join #CustomerPurchase cp on a.CustomerID = cp.CustomerID and a.ProductID = cp.ProductID
left join #Customers c on a.CustomerID = c.CustomerID
where a.CustomerID is not null
group by a.CustomerID, c.CustomerName, a.ProductName
having Sum(isnull(cp.Quantity,0)) = 0
Order by a.CustomerID, a.ProductName


--- Items only purchased
select a.CustomerID, c.CustomerName, a.ProductName , Sum(isnull(cp.Quantity,0)) as QuantityPurchased
from #tmpProductCustomer a
left join #CustomerPurchase cp on a.CustomerID = cp.CustomerID and a.ProductID = cp.ProductID
left join #Customers c on a.CustomerID = c.CustomerID
where a.CustomerID is not null
group by a.CustomerID, c.CustomerName, a.ProductName
having Sum(isnull(cp.Quantity,0)) <> 0
Order by a.CustomerID, a.ProductName


drop table #Customers
drop table #Products
drop table #CustomerPurchase
drop table #tmpProductCustomer[b] [/b]
shivanand_k
New Member
New Member

--
10 Dec 2012 08:08 AM
--The above query assumes that atleast on product has being purchased.
--Replace the insert in the #tmpProductCustomer as below.. it will include all the customer product combination.

-- Create a temp table to hold Product & customer combination (in real situation it can be a view)
create table #tmpProductCustomer (
CustomerID int,
ProductID int,
ProductName varchar(50)
)
insert into #tmpProductCustomer
select c.CustomerID,p.productID,p.ProductName from #Customers c, #Products p


Acceptable Use Policy
---