Reminders for next month's birthdays

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

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?

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?

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

For clarity, I would make the "Birthday Next Month" query its own list, then I would create individual lists for "Prospect/Donors for [Dev Staff Member]",  since the prospect/donor lists are probably useful by themselves.  Both the birthday and the prospect/donor lists should be dynamic, of course.  Then I would create another set of lists for the birthday report like this:

List HAS "Birthday Next Month"

and

List HAS "Prospect/Donors for [Dev Staff Member]"

Then I would create an output set useful to the Dev staff (bonus points if the actual Birthday was an element) and schedule a set of "Execute an Output Set" reports, one for each relevant staff member.

Ok, I think my brain is getting this. 

• A list called 'Birthday Next Month' (set to be staff-agnostic)
  • using y'alls cool SQL queries
• A list called 'Prospect Donors for X' (and '....Y', and '...Z' etc...)

Mix and match those to get the right pairings of staff and birthday months (with a third set of lists), then automate as needed. 

Does that make sense?

Yep!  Just remember when you want someone who is a member of two different lists you want to have two different criteria entries and use "HAS".

An update for everyone (I know you were waiting with bated breath). I've set up lists for our President's Council and Board of Trustee Individuals, 'Next Month's Birthdays', and the combinations of those. I also set it up to execute an 'Display Name, Birthday, Birthdate' Output Set in conjunction with both those combinations (PC+birthdays next month, BoT + Birthdays next month).

On the 27th, those two output sets will run, and we'll see if this works!