We help IT Professionals succeed at work.

Finding Customers who made a purchase and customer who did not make a purchase

fb1990
fb1990 asked
on
Hello EE,

Can someone please help me with a SQL code to get a list of customers who purchased at least 1 item in a giving month and customers who did not make a purchase in that same month? I want to show 1 for customers who made a purchase and 0 for customers who did not make a purchase.  I have 2 tables - a customer table and an order table.  The customer table contains all the customers while the order table contains only the customer who made a purchase in any giving month.  In my case, I want to show customers who made a purchase in the month of January 2020 customers who did not.

Both tables have the customer as an identifier while the orders table contains customerno, ordercount, and orderdate
Comment
Watch Question

David ToddSenior Database Administrator

Commented:
Hi

Does this work for you?
use AdventureWorks2017
go

select 
	c.CustomerID
	, count( h.SalesOrderID )
from sales.Customer c
left outer join sales.SalesOrderHeader h
	on h.CustomerID = c.CustomerID
	and h.OrderDate >= '2012-01-01'
	and h.OrderDate < '2012-02-01'
group by
	c.CustomerID
having 
	count( h.SalesOrderID ) > 0
;

Open in new window



hth
  David

Author

Commented:
Thank you for your assistance.  I did not see where this is referencing the customer table. I need to represent customers in the order table who made a purchase with 1 and customers who did not make a purchase, but on the customer table with a 0 in the result
Software Team Lead
Commented:
you could try something like this:

;with customer as
(
	Select 1 cust_id, 'Cust A' cust_name union all
	Select 2 cust_id, 'Cust B' cust_name union all
	Select 3 cust_id, 'Cust C' cust_name union all
	Select 4 cust_id, 'Cust D' cust_name union all
	Select 5 cust_id, 'Cust E' cust_name
), cust_order as
(
	Select 1 order_id, 1 cust_id, 'A' item_id, cast('12 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 2 order_id, 1 cust_id, 'B' item_id, cast('13 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 3 order_id, 2 cust_id, 'B' item_id, cast('14 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 4 order_id, 3 cust_id, 'A' item_id, cast('15 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 5 order_id, 1 cust_id, 'B' item_id, cast('16 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 6 order_id, 2 cust_id, 'C' item_id, cast('17 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 7 order_id, 2 cust_id, 'D' item_id, cast('18 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 8 order_id, 1 cust_id, 'E' item_id, cast('19 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 9 order_id, 2 cust_id, 'A' item_id, cast('15 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 10 order_id, 2 cust_id, 'B' item_id, cast('16 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 11 order_id, 2 cust_id, 'C' item_id, cast('17 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 12 order_id, 5 cust_id, 'A' item_id, cast('20 Jan 2020 13:02pm' as datetime) pur_date union all
	Select 13 order_id, 2 cust_id, 'A' item_id, cast('21 Jan 2019 13:02pm' as datetime) pur_date union all
	Select 14 order_id, 3 cust_id, 'A' item_id, cast('15 Nov 2019 13:02pm' as datetime) pur_date union all
	Select 15 order_id, 2 cust_id, 'B' item_id, cast('16 Dec 2019 13:02pm' as datetime) pur_date union all
	Select 16 order_id, 3 cust_id, 'C' item_id, cast('17 Jan 2020 13:02pm' as datetime) pur_date
)
Select a.cust_id, a.cust_name,
case when b.cust_id is null then 0 else 1 end make_a_purchase,
case when b.no_of_purchases is null then 0 else b.no_of_purchases end
From customer a
left join 
(
	Select cust_id, count(cust_id) no_of_purchases
	From cust_order
	Where pur_date >= '1 Jan 2020' and pur_date < '1 Feb 2020'
	group by cust_id
) b
on a.cust_id = b.cust_id
order by a.cust_id, a.cust_name

Open in new window

Author

Commented:
Thank you very much, Ryan.  The solution is very helpful!