I am having two table.
one table contains customer details(i.e)
id(customerid--primary key),name,email,phonenumber
And other table contains order table(i.e)
id,customerid(foreign key),ordervalue,orderdate
I need to get the list of customers who have not ordered for last one month(i.e) for the month of august. How can i do it.
This is the query i tried
select a.id,a.name,b.order_date from customers a
left join orders b
on a.id = b.customer_id
where b.order_date is null
This query will extract those customers who haven't order past one month from today:
SELECT a.id, a.name
FROM customers a
WHERE NOT EXISTS(SELECT *
FROM orders b
WHERE b.cid = a.id AND
orderdate BETWEEN now()- '1 month'::interval
AND now());
Here is the SQLfiddle
However, if you want to be more precise where you want last month's orders i.e. from 1st of last month to last date of last month then you can use this :
SELECT a.id, a.name
FROM customers a
WHERE NOT EXISTS(SELECT *
FROM orders b
WHERE b.cid = a.id AND
to_char(orderdate, 'Mon YYYY') =
to_char(now()- '1 month'::interval, 'Mon YYYY') );
Here is the SQLfiddle
EDIT
Please also have a look at Roman Pekar's answer which is more efficient.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments