sql 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 file 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.

     

    Attachments:
    You must be logged in to view attached files.
  • This doesn't seem too complicated, what have you tried so far?

  • I wrote a sql that  does the following, But I am not getting correct results:

    --get past 13 month contact records for all members/contacts in base table AllEnc.

    SELECT MemberNumber,

    ContactNumber

    ContactDate,

    INTO #enc18mts

    FROM AllEnc f

    WHERE ContactDate BETWEEN DATEADD(month, -18, f.ContactDate ) AND DATEADD(day, -1, f.ContactDate )

    Then I am extracting records from AllEnc a

    left join #enc18mts t on ContactNumber

    where t.ContactNumber is null

    Thanks for your help.

    • This reply was modified 6 months, 1 week ago by  dk98681.
    • This reply was modified 6 months, 1 week ago by  dk98681.
  • Never mind... sounds like the issue is actually not in the DATEADD part, but possibly in the JOIN.

  • What do you suggest?

     

    Thank You

  • Without a Contacts table, you can't do it.

    You'd get all the contacts who had been contacted in the last 18 months, and then exclude those ContactIDs from those in the Contacts table. Try writing that. (Even if you write one query at a time. I used to do that, and still do with complicated queries...)

  • The temp table #enc18mts I created is meant for contacts in past 18 minths.

    Then I am extracting records that are not in #enc18mts .

    And its not just "past 18 months", it  is 18 months for the contact date in the table. Basically I want to flag each contact record as new contact if this is after 18 months from last contact.

     

    Thank you again.

     

  • I'd probably do something with a GROUP BY clause and HAVING clause, maybe:

    SELECT MemberNumber, MAX(ContactNumber) AS last_ContactNumber, MAX(ContactDate) AS last_ContactDate
    FROM AllEnc f
    GROUP BY MemberNumber
    HAVING MAX(ContactDate) < DATEADD(month, -18, GetDate());

     

  • Thank you. That gives me the idea.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply