tsql to find members not contacted in past 18 months

I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date).

The table has millions of records (sample data image attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql?

Your help is highly appreciated.

Thanks.

Answer

You must have a Member table containing all your members, right? Assuming you do, just select all the members that don’t have contact in the last 18 months.

SELECT *
FROM Member m
LEFT JOIN Contacts c on m.MemberNumber = c.MemberNumber AND
  c.ContactDate > DATEADD(Month, -18, GETDATE())
WHERE c.ContactNumber IS NULL

I believe this would work too.

SELECT *
FROM   Member m
WHERE  NOT EXISTS 
  (
    SELECT *
    FROM   Contacts
    WHERE  MemberNumber = m.MemberNumber
            AND ContactDate > DATEADD(MONTH, -18, GETDATE())
  )