When viewing a small group, we wanted to be able to see the last time each member served on a team. And likewise, when viewing a team, we wanted to see the last time someone attended a small group.

We also have some standards of how often we want people to serve on a team and attend a life group. At Northview we want people doing those things in the last 90 days. This shortcode takes that into consideration and provides some visual feedback.

The shortcode will list the date and team/group name of the last time that person attended. If nothing is in the column, then there is no attendance data for that person. If the date and name are green, then it is within the last 90 days. If it is red, it is older than 90 days.

This allows you to look at a group or team and quickly see how many are engaging fully.

I created two short codes. One for last team attendance and one for last group attendance. I also added a parameter to show just the date of last attendance with no color, or the more detailed version described above.

The only difference in the short codes are the group types in the SQL query. I am only going to show you on of the short codes.

Areas you will edit:

1. The group type ids. If you want to include more than one type. Change the "= 267" to "IN (1,2,3)
2. The 90 day standard (you can remove this also)

The Shortcode

{% assign personId = id | AsString %}
{% assign details = detailed | AsString %}

{% sql return:'rows' %}
SELECT TOP 1 MAX(ao.OccurrenceDate) as 'Date', g.Name
FROM Attendance a 
JOIN AttendanceOccurrence ao ON a.OccurrenceId = ao.Id 
JOIN [Group] g ON ao.GroupId = g.Id 
JOIN PersonAlias pa ON a.PersonAliasId = pa.Id 
JOIN Person p ON pa.PersonId = p.Id 
WHERE 
    g.GroupTypeId = 267
	AND g.IsActive = 1
	AND g.IsArchived = 0
	AND a.DidAttend = 1
	AND p.Id = {{personId}}
GROUP BY g.Name
ORDER BY Date desc
{% endsql %}

{% for row in rows %}
    {% assign date = row.Date | Date:'M/dd/yy'%}
    {% if details == 'True' %}
        {% assign pastDate = 'Now' | Date | DateAdd:-90 %}
        {% if pastDate > date %}
            
{{date}} | {{row.Name}} 
        {% else %}
            
{{date}} | {{row.Name}} 
        {% endif %}
    {% else %}
        {{date}}
    {% endif %}
{% endfor %}

Using the shortcode

You need to be able to pass in the person Id into the shortcode. This will look different depending on where you are using it. We added it as a custom column to our group viewer. That would look like this "{[GetLastGroupAttendance id:'{{Row.PersonId}}' detailed:'True']}". If you didn't want the detailed version, you would just leave that off entirely.