Average Attendance For Organizations¶

This script shows the average attendance for the meetings for each organizations in the time period specified.

It can be run from the blue Toolbar on the Organization > Search page and will ask for a date range.

Sample Report

https://i.tpsdb.com/./2018-01-25_14-43-54.png

The recommended name is AverageAttendanceForOrgs

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

--class=StartEndReport
;WITH meets AS (
    SELECT
        m.OrganizationId
        ,Attended = m.MaxCount
        ,m.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)
    AND m.MaxCount > 0
)
SELECT
    m.OrganizationId
    ,p.Name Program
    ,d.Name Division
    ,o.OrganizationName
    ,o.LeaderName
    ,[Attends] = SUM(Attended)
    ,[Meetings] = COUNT(*)
    ,[AvgAttends] = AVG(m.Attended)
    --,[AvgAttends] = AVG(CONVERT(FLOAT, Attended))
FROM meets m
JOIN dbo.Organizations o ON o.OrganizationId = m.OrganizationId
LEFT JOIN dbo.Division d ON d.Id = o.DivisionId
LEFT JOIN dbo.Program p ON p.Id = d.ProgId
GROUP BY m.OrganizationId, d.Name, p.Name, OrganizationName, o.LeaderName
--HAVING o.LeaderName IS NOT NULL
ORDER BY p.Name, d.Name, o.OrganizationName

Latest Update

11/13/2020

Modify image link with secure protocol.