Question

Photo of David Stevens

0

Calculating Metrics on Group Member #'s vs Group Attendance

2

I'm customizing a large number of metrics with custom SQL to track group member #'s and group attendance #'s.  

Our ministries compare the group member (roster) counts and attendance against the previous week/month/year to track ministry health.

Currently it seems the only way to get those numbers is custom SQL for each group (especially the previous month/year's group member count).  Anyone know of another way to do this?

  • Photo of David Stevens

    0

    That query would work as long as the Metric is partitioned by Group.  Since we're multi-site we have everything partitioned by Campus (and I can't partition again by Group).

    For reference, here's the query I ended up with...it's a little more complicated because we have one Group Member attribute that tracks Campus and another that tracks Schedule:

        /* ====================================================================== */
    -- Returns group rosters by Campus and Service from the previous day
    -- Returns a timestamp of 00:00:00 when no schedule exists
    /* ====================================================================== */
    SELECT COUNT(1) AS Value, Campus.Id AS EntityId, 
        DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0) + LEFT(ISNULL(Schedule.Value, '00:00'), 5) AS ScheduleDate
    FROM GroupMember GM
    INNER JOIN [Group] G
        ON GM.GroupId = G.Id
    -- Filter by Campus
    LEFT JOIN (
        SELECT AV.EntityId AS MemberId, C.Id
        FROM [Attribute] CA
        INNER JOIN AttributeValue AV
            ON AV.AttributeId = CA.Id
            AND CA.[Key] = 'Campus'
            AND CA.EntityTypeQualifierColumn = 'GroupTypeId'
            AND CA.EntityTypeQualifierValue = {{GroupTypeId}}
        LEFT JOIN Campus C
            ON AV.Value = C.[Guid]
    ) Campus
        ON GM.Id = Campus.MemberId
    -- Filter by Schedule
    LEFT JOIN (
        SELECT AV.EntityId, DV.Value
        FROM DefinedValue DV
        LEFT JOIN (
            SELECT EntityId, r.value('.', 'VARCHAR(255)') AS Schedule
            FROM (
                -- Denormalize the comma-delimited GUID string
                SELECT Value, EntityId, CAST('<n>' + REPLACE(Value, ',', '</n><n>') + '</n>' AS XML) AS Schedules
                FROM [Attribute] SA
                INNER JOIN AttributeValue AV
                    ON AV.AttributeId = SA.Id
                    AND SA.[Key] = 'Schedule'
                    AND SA.EntityTypeQualifierColumn = 'GroupTypeId'
                    AND SA.EntityTypeQualifierValue = {{GroupTypeId}}
            ) AS nodes 
            -- Parse the xml nodes to join to Schedule
            CROSS APPLY Schedules.nodes('n') AS parse(r)        
        ) AV 
        ON AV.Schedule = DV.[Guid]
        WHERE EntityId IS NOT NULL
    ) Schedule
        ON GM.Id = Schedule.EntityId
    WHERE GM.GroupId = {{GroupId}}
        AND GM.GroupMemberStatus = 1
    GROUP BY Campus.Id, Schedule.Value
    ORDER BY Campus.Id, Schedule.Value
    • Dillan Cagnetta

      Dillan Cagnetta

      Hi David, there seems to be a syntax error.


      To clarify, this takes into account the Service Times defined for Campuses?

    • Photo of Michael Garrison

      0

      David,

      Custom SQL is likely the way to go, but you shouldn't have to customize it for each group.

      I understand that my output is only superficially related to the data you're trying to get, but if you want an example of how to target an entire class of attendance data with a single query you can check out what I posted here: http://shouldertheboulder.com/snippet-attendance-grid/

      Now, in my case, I specifically excluded the type of attendance data you're wanting, but I believe that group attendance is stored similarly so you could probably tweak the queries a bit to get what you want.

      I've never tried to go back and plug it back into the metrics tool, but maybe this helps you start getting an idea for another way...