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.
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());
LatestContactDate = MAX(y.ContactDate)
YourTable AS y
MAX(y.ContactDate) < @CutoffDate;