Average Monthly Attendance For Organizations¶

This script shows the average for the meetings in the month for the organizations by year and week. It is similar to the Average Monthly Attendance For Organization that works for the Current Org which only works for a single organization.

It works from the OrgSearch BlueToolbar and will ask for a date range.

The recommended name is AverageMonthlyAttendanceForOrgs

Use the following code to Create the SQL Script. See How to create a SQL Script.

--class=StartEndReport
;WITH meets AS (
    SELECT
        o.OrganizationName
        ,Attended = ISNULL(SUM(HeadCount), SUM(NumPresent))
        ,[Year] = DATEPART(YEAR, MeetingDate)
        ,[Month] = DATEPART(MONTH, MeetingDate)
    FROM Meetings m
    JOIN dbo.Organizations o ON o.OrganizationId = m.OrganizationId
    WHERE m.OrganizationId IN (SELECT Value FROM dbo.SplitInts(@orgids))
    AND MeetingDate >= @MeetingDate1
    AND MeetingDate < DATEADD(HOUR, 24, @MeetingDate2)
    GROUP BY OrganizationName, MeetingDate
    HAVING ISNULL(SUM(HeadCount), SUM(NumPresent)) > 0
)
SELECT
    meets.OrganizationName
    ,[Year] = ISNULL(CONVERT(VARCHAR, [Year]),
                IIF(GROUPING([Year]) = 0, '', 'Total'))
    ,[Month] = ISNULL(CONVERT(VARCHAR, [Month]),
                IIF(GROUPING([Month]) = 0, '', 'Total'))
    ,[Total Attended] = SUM(Attended)
    ,[Num Meetings] = COUNT(*)
    ,[Average Attendance] = AVG(Attended)
FROM meets
GROUP BY OrganizationName, [Year], [Month] WITH ROLLUP
ORDER BY GROUPING(OrganizationName), meets.OrganizationName, GROUPING([Year]), [Year] DESC,
    CONVERT(INT, GROUPING([Month])), CONVERT(INT, [Month]) DESC