Average Monthly Attendance for an Org

This script will run for a single organization, the Current Org, which is the last org viewed. This is similar to the script that you can run for a group of orgs, and for which you specify the date range.

See also

Average Monthly Attendance For Organizations

The report sorts from most current year/month and will present data from the inception of the org.

https://i.tpsdb.com/2017-03-28_12-48-34.png

Sample Avg Monthly Att

As in the sample above, you see the year in the first column, followed by the month (integer), Total Attended, the Number of Meetings that month, and then the Average Attendance.

Each year has a total number attended, the total meetings, and the yearly average.

Create the Script

Step 1

Go to Administration > Set-Up > Special Content, select SQL Scripts, and then click the green + New Sql Script File.

Step 2

Enter the name as AverageMonthlyAttendanceForOrg and click Submit.

Step 3

Copy the code below and paste it into the new SQL file you just created.

Then click the blue Save SQL Script button at the bottom of the file.

Step 4

Go to an organization (so that there is a Current Org), then return to the script you just created.

Now click the button to Run Script. The report will run for the Current Org.

Scroll to the bottom of the report and click the link Add Report to Menu in order for the report to display under Reports in the main menu.

Note

To run for an actual org, go to that org and then select from the Main Menu Reports > Other Reports > Average Monthly Attendance for Org.

Code

;WITH meets AS (
    SELECT
         Attended = ISNULL(SUM(HeadCount), SUM(NumPresent))
        ,[Year] = DATEPART(YEAR, MeetingDate)
        ,[Month] = DATEPART(MONTH, MeetingDate)
    FROM Meetings
    WHERE OrganizationId = @CurrentOrgId
    GROUP BY MeetingDate
    HAVING ISNULL(SUM(HeadCount), SUM(NumPresent)) > 0
)
SELECT
     [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 [Year], [Month] WITH ROLLUP
ORDER BY GROUPING([Year]), [Year] DESC,
    CONVERT(INT, GROUPING([Month])), CONVERT(INT, [Month]) DESC


Latest Update

11/13/2020

Modify image link with secure protocol.