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 :doc:`../CreateSqlScript`. .. code-block:: sql --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