• Replies 11 replies
  • Subscribers 961 subscribers
  • Views 126 views
  • Users 0 members are here
Related

Reminders for next month's birthdays

Nathanael Pearson
$organization

Hi folks,

We want to set up an automated report that looks at a list of constituents and pulls in those with a birthday next month. How would we best set this up?

I know we can set up birthdays as 'Attributes', but I'm not sure how to pull that into a list by the month, not the full date. In other words, how do I pull a list of 'these people have a birthday in this MONTH', not 'these people have a birthday after or before this DATE'? I can see how to do before or after 1/1/2020, but not 'all birthdays in January'. Another option would be to create a 'Birth Month' Attribute, and assign that based on each Birthdate. A bit clunky, but doable.

We can also set up reminders for each individual constituent (through Steps, CSIs, or Plans). Is there a way to aggregate those things into a list or something? 

Parents
  • $organization

    Hi Nathaniel,

    Assuming that your Birthdate Attribute has a keyword_no of 1 in the T_KEYWORD table, the following SQL will return people with a birthday the month following the date the list is run.

    select distinct dob.customer_no
    from T_CUSTOMER c
    join TX_CUST_KEYWORD dob on dob.customer_no=c.customer_no and dob.keyword_no = 1
    where datepart(mm,key_value) = datepart(mm,getdate())+1
    and c.inactive = 1

    Give it a whirl and have fun!

    Martin

  • $organization in reply to Martin Keen (Past Member)

    Thanks, Martin!

    I confess I haven't used SQL in a long while. 

    The use case would be sending each of our DEV folks a list each month of the people they're connected to who have a birthday in the following month. Could I use this script and some default criteria like:

    "

    [Your script] 

    AND

    [List] = ' list of prospects/donors for DEV staff X'

    "

    Then, I could copy/paste that for each staff person (X, Y, Z), and the lists tailored for them. How would that work?

  • $organization in reply to Nathanael Pearson

    in list manager you can do this in the show query view:
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   t_list_contents AS a1 WITH (NOLOCK)
            WHERE  a1.list_no IN (XXXXX)) AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
           AND EXISTS (select customer_no
    from TX_CUST_KEYWORD
    where keyword_no in (1) AND key_value like '%%%%-XX-%%')

    change list XXXX to list # and XX in key value to month

Reply
  • $organization in reply to Nathanael Pearson

    in list manager you can do this in the show query view:
    SELECT DISTINCT a.customer_no
    FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
           INNER JOIN
           (SELECT a1.customer_no
            FROM   t_list_contents AS a1 WITH (NOLOCK)
            WHERE  a1.list_no IN (XXXXX)) AS e
           ON e.customer_no = a.customer_no
    WHERE  a.inactive = 1
           AND EXISTS (select customer_no
    from TX_CUST_KEYWORD
    where keyword_no in (1) AND key_value like '%%%%-XX-%%')

    change list XXXX to list # and XX in key value to month

Children
No Data