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.

.. seealso::
    :doc:`../BlueToolBarOrgSearch/AvgMonthlyAttendanceOrgs`


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


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

    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 :ref:`code below <AvgMonAttOrgScript>` 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`.

.. _AvgMonAttOrgScript:

Code
----

.. code-block:: sql

    ;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.