DECLARE @GroupType int;

{% assign gtId = 'Global' | PageParameter:'GroupType' %}

{% if gtId == '' %}
    SET @GroupType = 0;
{% else %}
    SET @GroupType = {{ gtId }};
{% endif %}

WITH cte_Ranked AS (
    SELECT
        g.Id 'GroupId'
        ,ao.Id 'AttendanceOccurrenceId'
        ,RANK() OVER ( PARTITION BY g.Id ORDER BY ao.OccurrenceDate DESC ) 'Rank'
    FROM
        [Group] g
        LEFT JOIN [AttendanceOccurrence] ao
            ON g.Id = ao.GroupId
            --AND ao.DidNotOccur = 0
            -- Commented out the above line because when active it discludes the occurrences where a leader marked
            -- "did not attend". Tested, and commenting it out doesn't inaccurately report missed attendance
            
    WHERE
        g.GroupTypeId = @GroupType
        AND g.IsActive = 1
        AND g.IsArchived = 0
)
SELECT 
    g.Id
    ,r.AttendanceOccurrenceId
    ,g.Name
    ,ao.OccurrenceDate 'Last Attendance'
    ,(
        SELECT COUNT( 1 )
        FROM [Attendance]
        WHERE
            OccurrenceId = r.AttendanceOccurrenceId
            AND DidAttend = 1
    ) 'Count'
    ,ao.Notes
FROM
    [cte_Ranked] r
    JOIN [Group] g ON r.GroupId = g.Id
    LEFT JOIN [AttendanceOccurrence] ao ON r.AttendanceOccurrenceId = ao.Id
WHERE r.Rank = 1
  AND r.AttendanceOccurrenceId IS NOT NULL
ORDER BY ao.OccurrenceDate DESC