Find inactive customers 1

[StuartBombay]

We have a customer table, orders table, and customer plan table. We need to find customers on a certain plan that have not placed an order in over 3 months. Some customers have never placed an order at all, we need to know who they are too.
Here is my code, the problem is if a customer has placed an order within the last 3 months, they still show in the results but with an order date of 'null'. Ideally if they've placed an order within the last 3 months they wouldn't show at all.
Help please?

SELECT
a.CustomerID
, LastOrderDate
FROM Customer a

INNER JOIN
(
SELECT
y.PlanID
FROM Plans y
WHERE y.PlanName = 'Internet'
) c
ON a."Plan" = c.PlanID

LEFT JOIN
(
SELECT
x.CustomerID,
MAX(x.OrderDate) AS LastOrderDate
FROM Orders x
GROUP BY x.CustomerID
HAVING MAX(x.OrderDate) <= (DATEADD(month, -4, GETDATE()))
)b
ON
a.CustomerID = b.CustomerID
ORDER BY LastOrderDate

 

[gmmastros]

Try this.

SELECT
a.CustomerID
, LastOrderDate
FROM Customer a

INNER JOIN
(
SELECT
y.PlanID
FROM Plans y
WHERE y.PlanName = 'Internet'
) c
ON a."Plan" = c.PlanID

LEFT JOIN
(
SELECT
x.CustomerID,
MAX(x.OrderDate) AS LastOrderDate
FROM Orders x
GROUP BY x.CustomerID
)b
ON
a.CustomerID = b.CustomerID
[!]Where b.LastOrderDate <= (DATEADD(month, -4, GETDATE()))[/!]
ORDER BY LastOrderDate

If this works, and you would like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StuartBombay]

Beautiful, thank you.
I have a customer in the customer table who has the 'internet' plan but has never placed an order (so is not in the orders table). Can that be incorporated into this query or would I be better off using a separate query using some sort of 'not in' test?

 

[gmmastros]

Change...

Where b.LastOrderDate <= (DATEADD(month, -4, GETDATE()))


To...

Where [!]([/!]b.LastOrderDate <= (DATEADD(month, -4, GETDATE()))
[!]Or b.LastOrderDate Is NULL)[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StuartBombay]

Perfect