SQL Statement Help - Select Only Customers with no order for past 3 months

Question

Hope you all are fine. I need to make a query. Kindly help me with this.

Here is the Scenario. We have two Tables

Customers
Orders

I want to get only those customers which have not ordered anything for the past three months

Kindly help me. I am Stuck. Thanks in advance

@op - can you please provide us with the table structures because the queries will vary depending on your tables — judda, May 09 '11 at 11:09
Agree with *diEcho*: do you want it in MySQL or in SQL Server? Have you by chance confused `mysql` with `mssql`? — Andriy M, May 09 '11 at 11:09

score 3 · Answer 1 · answered May 09 '11 at 11:04

Without knowing your exact data structure I am guessing something along the lines of:

Select  CustomerCode,
    CustomerName

From dbo.Customers
where CustomerCode Not in (
               Select CustomerCode
               From dbo.Orders
               Where OrderDate > Cast(Floor(Cast(dateAdd(Month,-3, GetDate()) as Float))as DateTime)
              )

score 2 · Answer 2 · answered May 09 '11 at 11:43

2

SELECT customer_number
  FROM Customers
EXCEPT 
SELECT customer_number
  FROM Orders
 WHERE DATEADD(MONTH,-3, CURRENT_TIMESTAMP) < order_date;

answered May 09 '11 at 11:43

onedaywhen

55,269
12
100
138

score 1 · Accepted Answer · answered May 09 '11 at 11:06

1

;WITH CTE_LastOrder (CustomerId, LastOrderDate) As
(
SELECT CustomerId, MAX(OrderDate) LastOrderDate
FROM Orders 
GROUP By CustomerId
)
SELECT * from Customers C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > Cast(Floor(Cast(dateAdd(Month,-3, GetDate()) as Float))as DateTime)

Above is the basic sql for SQL Server. There might be slight difference in the syntax.

answered May 09 '11 at 11:06

Tushar

1,242
1
8
19

first slight difference is going to be the lack of support for CTE in mysql. – Unreason May 09 '11 at 11:45

SQL Statement Help - Select Only Customers with no order for past 3 months

3 Answers3