Calculate Attendance Frequency from Check-in Data

Shared by Joshua Stogsdill, The Creek Church
5 years ago

8.0 Kids, Operations Intermediate

"How often do our people attend?"

That's a common question without an easy way to measure. (Wifi Presence looks like a good option, but our church isn't there yet.) That leaves kids check-in at our services as one of the few reliable ways to calculate attendance frequency. In order to make that data easily accessible, we created a page with a dynamic data block (actually two dynamic data blocks) to easily look at family attendance averages:

2019-01-14 16_52_06-Window.png

How We Did It

The SQL Code

The whole process is based around some SQL code that fetches every family in the database and calculates how many unique Sunday dates can be found from their check-in history. We have two different check-in areas at our church, and therefore, two different group types for which we need to check attendance (for us, group types 19 and 20). You will need to update the line "AND cg.[GroupTypeId] IN (19,20)" below with the group types that represent your check-in areas.

SELECT

    g.[Name],

    ( -- Sub QUEry

        SELECT

            COUNT(DISTINCT CONVERT(date,a.[StartDateTime] ) )

        FROM

            [Attendance] a

        JOIN

            [AttendanceOccurrence] ao on ao.[Id] = a.[OccurrenceId]

        JOIN

            [Group] cg on cg.[Id] = ao.[GroupId]

        JOIN

            [PersonAlias] pa ON pa.[Id] = a.[PersonAliasId]

        JOIN

            [Person] p ON p.[Id] = pa.[PersonId]

        WHERE

            a.[DidAttend] = 1

            AND cg.[GroupTypeId] IN (19,20)

            AND a.[StartDateTime] BETWEEN @startDate and @endDate

            AND DATEPART(weekday,a.[StartDateTime]) = 1

            AND p.[Id] in 

                --Where Subquery

                (

                    SELECT

                        jp.[Id]

                    FROM

                        [Person] jp

                    JOIN

                        [GroupMember] jgm on jgm.[PersonId] = jp.[Id] AND jgm.[GroupId] = g.[Id]

                    JOIN

                        [Group] jg on jg.[Id] = jgm.[GroupId]

                    WHERE

                        (@campus = '-1')

                        OR

                        (@campus != '-1' AND jg.[CampusId] = @campus) 

                )

                -- End Where Subquery

    )-- End Subquery 

     as WeeksAttending

    FROM

        [Group] g

    WHERE 

        g.[GroupTypeId] = 10

       

    ORDER BY WeeksAttending DESC

We use this same code in two different dynamic data blocks: 1) to calculate the desired average attendance frequency, and 2) to list families by order of check-in attendance. The second is probably less useful but kind of fun to see.

Configuration and Page Parameters

In order to allow the results to be configurable, we included three user-adjustable options: 1) number of visits required to consider a family for averaging purposes, 2) the date range to consider, 3) the campus to consider. (Just for clarity: campus only considers the campus listed on the family's profile, not the specific location at which they checked in.)

We used a combination of form elements, Javascript, and page parameters to pass these configurations on to the dynamic data block. You will need to set the "Parameters" field on both dynamic data blocks to the following:

campus=-1;startDate=2018-01-01;endDate=2019-01-01;

Formatted Output

For the summary at the top of the page that actually does the frequency calculations, we use the following in the "Formatted Output" section of the dynamic data block:

{% assign sum = 0 %}
{% assign count = 0 %}
{% assign minTimes = 'Global' | PageParameter:'minTimes' | Default:'4' %}
{% for row in rows %}
    {% if row.weeksAttending > minTimes %}
        {% assign count = count | Plus:1 %}
        {% assign sum = sum | Plus:row.weeksAttending %}
    {% endif %}
{% endfor %}
{% assign weeks = sum | DividedBy:count,2 %}
{% assign spacing = 52 | DividedBy:weeks,2 %}
<style>
    .metric {
        border: 1px solid #ccc;
        padding: 12px;
        margin-bottom: 12px;
    }
    
    .metric h5 {
        font-size: 24px;
        margin-top: 0;
        margin-bottom: 0;
        width: 100%;
        white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
        line-height:1.4em;
    }
    
    .metric .value {
        font-size: 48px;
        font-weight: 800;
        line-height: 1em;
    }
    
    .metric .value small{
        display: block;
        font-weight: 300;
        font-size: 14px;
        line-height: 1em;
    }
    
    .metric .icon {
        float: right;
        opacity: .3;
        font-size: 65px;
        border-radius: 0;
        width: 85px;
        height: 65px;
        margin-top:-15px;
    }
</style>
<div class="col-lg-4">
    <div class="metric">
        <h5>Configure Results</h5><br />
        <p>
            <span style="font-weight:bold;">Visits Required to Consider:</span><br />
            <input type="text" name="minTimes" value="{{ minTimes }}" />
        </p>
        {% assign pageCampus = 'Global' | PageParameter:'campus' | Default:'-1' %}
        <p>
            <span style="font-weight:bold;">Campus:</span><br />
            <select name="campus">
                <option value="-1">All Campuses</option>
                {% for campus in Campuses %}
                    <option value="{{campus.Id}}" {% if pageCampus == campus.Id %}selected{% endif %}>{{ campus.Name }}</option>
                {% endfor %}
            </select>
        </p>
        
    
        {% assign defaultEndDate = '2019-01-01' | Date:'M/d/yyyy' %}
        {% assign defaultStartDate = '2019-01-01' | DateAdd:-1,'y' | Date:'M/d/yyyy' %}
        {% assign startDate = 'Global' | PageParameter:'startDate' | Default:defaultStartDate %}
        {% assign endDate = 'Global' | PageParameter:'endDate' | Default:defaultEndDate %}
        <p>
            <span style="font-weight:bold;">Date Range:</span><br />
            <input type="text" class="dpz" name="startDate" value="{{ startDate }}" style="margin-right:10px;">
            <input type="text" class="dpz" name="endDate" value="{{ endDate }}">
        </p>
        
        <p>
            <input type="button" value="Recalculate" onclick="recalc();">
        </p>
    </div>
</div>
<div class="col-lg-4">
    <div class="metric">
        <h5>Families Considered</h5>
        
        <i class="icon fa fa-users"></i>
        <div class="value">
            {{ count }}
            <small>Families</small>
        </div>
    </div>
</div>
<div class="col-lg-4">
    <div class="metric">
        <h5>Average Times Checking In</h5>
        
        <i class="icon fa fa-check-square"></i>
        <div class="value">
            {{ weeks }}
            <small>Weeks</small>
        </div>
    </div>
</div>
<div class="col-lg-4">
    <div class="metric">
        <h5>Average of Once Every</h5>
        
        <i class="icon fa fa-calendar"></i>
        <div class="value">
            {{ spacing }}
            <small>Weeks</small>
        </div>
    </div>
</div>



<script type="application/javascript">
    function updateURLParameter(url, param, paramVal){
        var newAdditionalURL = "";
        var tempArray = url.split("?");
        var baseURL = tempArray[0];
        var additionalURL = tempArray[1];
        var temp = "";
        if (additionalURL) {
            tempArray = additionalURL.split("&");
            for (var i=0; i<tempArray.length; i++){
                if(tempArray[i].split('=')[0] != param){
                    newAdditionalURL += temp + tempArray[i];
                    temp = "&";
                }
            }
        }
       
        if(paramVal != "-1")
        {
            var rows_txt = temp + "" + param + "=" + paramVal;
        }
        else
        {
            var rows_txt = "";
        }
        return baseURL + "?" + newAdditionalURL + rows_txt;
    }
    
    function recalc()
    {
    
        var minTimes = $('input[name=minTimes]').val();
        var campus = $('select[name=campus]').val();
        var startDate = $('input[name=startDate]').val();
        var endDate = $('input[name=endDate]').val();
    
        var newUrl = window.location.href;
        newUrl = updateURLParameter(newUrl,'minTimes',minTimes);
        newUrl = updateURLParameter(newUrl,'campus',campus);
        newUrl = updateURLParameter(newUrl,'startDate',startDate);
        newUrl = updateURLParameter(newUrl,'endDate',endDate); 
        window.location.href = newUrl;
    }
    
    $(document).ready(function(){
        
        $( ".dpz" ).each(function(){
            var initialDateValue = $(this).val();
            $(this).datepicker();
            $(this).datepicker('setDate', initialDateValue);
        });
    });
</script>

That code iterates over the SQL results to calculate the average. I'm sure there is an easier way to do it in the SQL, but I struggled to find it. The code also formats the results and creates/handles the configuration options. The configuration works by refreshing the page with page parameters appended to the URL that are then processed by the "Parameters" options on the dynamic data blocks as set above.

Family List

We displayed a family list below the averages mainly for the novelty. It consists of a dynamic data block with same SQL code and "Parameters" from above, except, the "Formatted Output' block is left blank and the block allowed to output the default layout of SQL results.

Recipe

Calculate Attendance Frequency from Check-in Data

Calculate Attendance Frequency from Check-in Data

"How often do our people attend?"

How We Did It

The SQL Code

Configuration and Page Parameters

Formatted Output

Family List

Login to add a comment...