Average Annual Attandance By Org¶

This is the Average Annual Attendance by Organization for each Active organization in the church. You can add this to Reports in the Main Menu.

The report will list each organization and its average attendance based on the total number of attendances and the total number of absences for the past 365 days.

https://i.tpsdb.com/2017-10-01_00-48-53_01.png — Sample Report¶

Use the following code to Create the SQL Script. The recommended name is AvgAnnAttByOrg.

See How to create a SQL Script.

select
    tt.OrganizationName
    ,tt.nattends
    ,tt.nabsents
    ,tt.nattends + tt.nabsents ntotal
    , (convert(real, nattends) / (nattends + nabsents)) * 100  avg
from (
    select
    OrganizationName
    ,(select count(*) from Attend a
        where a.OrganizationId = o.OrganizationId
        and attendanceflag = 1
        and attendancetypeid not in (40,50,60)
        and meetingdate > dateadd(yy, -1, getdate())) nattends

    ,(select count(*) from Attend a
        where a.OrganizationId = o.OrganizationId
        and attendancetypeid not in (40,50,60)
        and attendanceFlag = 0
        and meetingdate > dateadd(yy, -1, getdate())) nabsents
    from Organizations o
    where exists(
        select null
        from DivOrg dd
        join Division d on d.Id = dd.DivId
        join progdiv pd on pd.Divid = d.Id
        where dd.OrgId = o.OrganizationId
    )
) tt
where tt.nattends > 0
ORDER BY (convert(real, nattends) / (nattends + nabsents)) DESC

Latest Update

11/13/2020

Modify image link with secure protocol.