mysqlsqlsql-server-2005query-help

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

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

Solution 
  • ;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.