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

1. Customers
2. 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

Answer 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.

Answer 2

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)
              )

Answer 3

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