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
AverageAttendanceCompareand 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.
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.
-- 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
Modify image link with secure protocol.