Sql to find members not contacted in past 18 months

1

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

The table has millions of records (sample data screenshot 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
.

sample data
sample data380×631 111 KB

2

Something like this might be the best option. You can also try to use the NOT EXISTS construct where you look for records that match a criteria that records should exist after the cut-off date, or row_number function to order the records in descending order of contact date and pick that fall outside the cut-off date.

DECLARE @CutoffDate DATE;
SET @CutoffDate = DATEADD(MONTH,-18,GETDATE()); 

SELECT 
    y.ContactNumber,
    y.MemberNumber,
    LatestContactDate = MAX(y.ContactDate)
FROM
    YourTable AS y
GROUP BY
    y.ContactNumber,
    y.MemberNumber
HAVING
    MAX(y.ContactDate) < @CutoffDate;
2 Likes
3

Thank You