Question

Photo of Ken Roach

0

How can I get a list of people with birthdays in the last week or the coming week?

5

The videos provide a great example of how to get a list of everyone with a birthday in a month.

But what if you only want to see a list of people with birthdays in the last week (that you missed) or in the coming week (that you'll be better at contacting)?

  • Photo of Ken Roach

    0

    (Options to make this easier may be coming as additional reporting Person fields. Until they do...)

    Here's one possible solution:

    Create a Dynamic Data Block as follows:

    dyna1.jpg

     

    dyna2.jpg

    QUERY

    SELECT [Id],[Nickname],[LastName],[Email],[BirthDate],[BirthDay],[BirthMonth],
        DATEDIFF(dd, GETDATE(), DATEADD(YEAR, (YEAR( GETDATE() )-YEAR( [BirthDate] )), [BirthDate]) )   -1     as DaysFromToday
    FROM [Person]
    WHERE
        DATEADD(YEAR, (YEAR( GETDATE() )-YEAR( [BirthDate] )), [BirthDate])
        BETWEEN DATEADD(DAY,  -7 , GETDATE()) AND DATEADD(DAY,  7  ,GETDATE() )
    ORDER BY DATEADD(YEAR, (YEAR( GETDATE() )-YEAR( [BirthDate] )), [BirthDate]) DESC

    (Note: I have to -1 from DaysFromToday because we (in New Zealand) are one day ahead of the date of our Arvixe server (in the US).  If your date is the same as your server's date you won't need to do this.

    Change -7 and 7 in the BETWEEN clause to whatever numbers you want for the date range you want.

     

    FORMATTED OUTPUT

    This links through to sending an email.

    dyna4.jpg

     

  • Photo of Ken Roach

    0

    Don, you should have a First Visit person attribute:  Admin Tools > General Settings > Person Attributes > look for First Visit.  So the field name you would be using instead of BirthDate will be FirstVisit, and yes you do need to do a join.

    The above SQL does not cope with ranges that cross over the year - e.g. pick up the FirstVisit dates in the next 50 days, if those days are in Jan.  I'll do some testing and then post a corrected SQL statement for you in the next few days. K

  • Photo of Brent Pirolli

    0

    For those looking to only list staff birthdays on their internal page, we were able to display birthdays from users of a security group vs those tagged as staff (we don't use tags currently). We also have a contact link for each person that pre-fills them as a recipient should someone wish to send them a message. And we also modified it to show birthdays from the past week (in case you missed one) as well as the upcoming month.

    bdays.jpg


    The SQL: (Our security group ID is 3. You will have to modify to be the group ID of your staff group you wish to use. Go to "Admin Tools - Security - Security Roles" and choose your role you wish to pull from. The URL of their page will look something like: https://URL/page/111?GroupId=3 and you can see the GroupID= to get the number you want to use.)

    SELECT p.[Id], [NickName], [LastName], [Email], [BirthDay], [BirthMonth]
    FROM [Person] p
    JOIN [GroupMember] gm on p.[Id] = gm.[PersonId]
    WHERE (([BirthMonth] = MONTH(GETDATE()) AND [BirthDay] >= DAY(GETDATE()) - 7) OR ([BirthMonth] = MONTH(GETDATE()) + 1) AND [BirthDay] <= DAY(GETDATE()))
    AND gm.[GroupId] = 3
    ORDER BY BirthMonth ASC, BirthDay ASC;


    And the formatted output:

    <div class="panel panel-block"> <div class="panel-heading"><h4 class="panel-title"><i class="fa fa-birthday-cake"></i> Recent & Upcoming Staff Birthdays</h4></div>
    <ul class="list-group">
        {% for row in rows %}
            <li class="list-group-item">
                <a href="/Person/{{ row.Id }}">
                    {{ row.NickName }}
                    {{ row.LastName }}
                </a>
                &nbsp;{{ row.BirthMonth }}/{{ row.BirthDay }}&nbsp;
                <a href="/Communication?person={{ row.Id }}"><i class="fa fa-envelope"></i></a>
                {% assign date = 'Now' | Date:' M/d' %}
                {% capture birthdate %}{{ row.BirthMonth }}/{{ row.BirthDay }}{% endcapture %}
                {% if date == birthdate %}
                &nbsp;<small><i class="fa fa-birthday-cake"></i> Happy Birthday!</small>
                {% endif %}
            </li>
        {% endfor %}
    </ul>
    </div>


  • Photo of Brian Wilkie

    0

    I haven't watch the video yet - I forgot to look for that resource when preparing this exact report request. 

    A simple report would be based on a dataview where the members and attendees (or whatever) is filtered by two criteria - (person Field) either the 'days until birthday' is greater than 357 or less than 8. 

    you can sort the report by 'birth month' and 'birth day' (don't show those fields in the grid, tho)

    I show the name,  'birth date', email, Mobile phone and home phone. (I wish there was a 'Primary phone' choice)

  • Photo of Don Smallman

    0

    Ken,

    I would like to do the same thing for a list of first time visitors, but I cannot find the field for the date of the first visit.  Can you tell me where it is?  I assume I neeed to join another table for this.

    Thanks,

    Don