Post reply

sql to find members not contacted in past 18 months

  • July 15, 2020 at 8:19 pm

    #3770356

    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.

  • July 15, 2020 at 8:35 pm

    #3770366

    This doesn't seem too complicated, what have you tried so far?

  • July 15, 2020 at 8:46 pm

    #3770371

    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 4 years, 4 months ago by  dk98681.
    • This reply was modified 4 years, 4 months ago by  dk98681.

  • July 15, 2020 at 8:48 pm

    #3770373

    Never mind... sounds like the issue is actually not in the DATEADD part, but possibly in the JOIN.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • July 15, 2020 at 8:53 pm

    #3770381

    What do you suggest?

     

    Thank You

  • July 15, 2020 at 9:00 pm

    #3770384

    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...)

  • July 15, 2020 at 9:05 pm

    #3770386

    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.

     

  • July 15, 2020 at 9:07 pm

    #3770390

    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());

     

  • July 15, 2020 at 11:38 pm

    #3770418

    Thank you. That gives me the idea.

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

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