Average Attendance Compare¶

This SQL script will look at organizations within the specified Division(s) and compare their current average attendance (past 365 days) with the previous 365 days.

You can changed the time period (number of days) in line 3 of the code. It is currently set to 365. You will need to enter the Division ID#(s) in line 4 of the code. If you use more than one ID# separate with a comma, no spaces.

Create Script for Average Attendance Comparison¶

Step 1

Go to Administration > Setup > Special Content > Sql Scripts and select the green + New Sql Script File button.

Enter the name AverageAttendanceCompare and press Submit.

Step 2

Select and copy the entire code below and paste it into your new file.

Step 3

Edit line 4 and enter the Division ID#s you want to include in the report (between the single quotes - ‘ ‘).

Optional: If you want to look at a different time period you can change line 2 from 365 to another number of days to look back.

Press the blue Save Sql Script button at the bottom of the file.

Step 4

Now you can run the script. Just click the Run Script button at the top of the file.

Note

After you run the report, you have the option to Add to Menu. If you do, it will display under Reports on the main menu. However, because this is looking only at the declared Divisions, you may want to always run this from the Special Content file and not add it to the menu. This allows you to verify the Divisions that the script will be using each time you run it. You can easily change these as needed and run the script.

Per-1 is the current 365 days, per-2 is the previous 365 days. The report gives the total number present, the total number of meetings, and then the average attendance.

https://i.tpsdb.com/2017-03-30_09-13-11.png — Sample Average Attendance Compare Report¶

Code¶

-- period 1 is the most recent year
-- period 2 is the previous year
DECLARE @days INT = 365
DECLARE @divs varchar(50) = ''

DECLARE @divids TABLE (id INT)
INSERT @divids ( id ) SELECT Value FROM dbo.SplitInts(@divs)

;WITH period1 AS (
    SELECT
        o.OrganizationId
        ,SUM(m.NumPresent) [present]
        ,COUNT(m.MeetingId) [meetings]
        ,CONVERT(FLOAT, SUM(m.NumPresent)) / COUNT(m.MeetingId) [average]
        ,MIN(m.MeetingDate) [firstdt]
        ,MAX(m.MeetingDate) [lastdt]
    FROM dbo.Organizations o
    JOIN dbo.Division d ON d.Id = o.DivisionId
    JOIN dbo.Program pp ON pp.Id = d.ProgId
    JOIN dbo.Meetings m ON m.OrganizationId = o.OrganizationId
    WHERE m.MeetingDate >= DATEADD(DAY, -@days, GETDATE())
    AND m.MeetingDate < GETDATE()
    AND m.MaxCount > 0
    GROUP BY o.OrganizationId
), period2 AS (
    SELECT
        m.OrganizationId
        ,SUM(m.NumPresent) [present]
        ,COUNT(m.MeetingId) [meetings]
        ,CONVERT(FLOAT, SUM(m.NumPresent)) / COUNT(m.MeetingId) [average]
        ,MIN(m.MeetingDate) [firstdt]
        ,MAX(m.MeetingDate) [lastdt]
    FROM dbo.Meetings m
    WHERE m.MeetingDate >= DATEADD(DAY, -@days * 2, GETDATE())
    AND m.MeetingDate < DATEADD(DAY, -@days, GETDATE())
    AND m.MaxCount > 0
    GROUP BY m.OrganizationId
)
SELECT
    o.OrganizationId
    ,p.Name Program
    ,d.NAME Division
    ,o.OrganizationName

    ,p1.present [per1-present]
    ,p2.present [per2-present]

    ,p1.meetings [per1-meetings]
    ,p2.meetings [per2-meetings]

    ,p1.average [per1-avg]
    ,p2.average [per2-avg]

FROM period1 p1
JOIN period2 p2 ON p2.OrganizationId = p1.OrganizationId
JOIN dbo.Organizations o ON o.OrganizationId = p1.OrganizationId
JOIN dbo.Division d ON d.Id = o.DivisionId
JOIN dbo.Program p ON p.Id = d.ProgId
WHERE EXISTS(
    SELECT NULL FROM dbo.DivOrg
    WHERE OrgId = o.OrganizationId
    AND DivId IN (SELECT id FROM @divids)
)
ORDER BY p.Name, d.Name, o.OrganizationName

Latest Update

11/13/2020

Modify image link with secure protocol.