Org Attend Counts¶

This script runs from the OrgSearch BlueToolbar.

It shows each member of or visitor to an Organization and displays the attendance counts during the specified date range. The # Sundays count will be a link to the Attendance History page for that person in the Org. Mom and Dad’s names are displayed as well.

The comment at the top of the script causes the report to pop-up a dialog box asking for Start and End dates of meetings inclusive.

This report has meeting times hardwired for 9:30 and 11:00 but you can customize it for your church.

Finally, as with all SqlScript reports, you have a Download to Excel button.

The recommended name is OrgAttendCounts

https://i.tpsdb.com/2016-06-29_11-29-15.png — Sample Report¶

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

--class=StartEndReport

IF(OBJECT_ID('tempdb..#t') IS NOT NULL)
    DROP TABLE #t

SELECT
    AttendId
    ,PeopleId
    ,OrganizationId
    ,MeetingDate
    ,Sunday = dbo.SundayForDate(a.MeetingDate)
    INTO #t
FROM dbo.Attend a
JOIN dbo.SplitInts(@OrgIds) ON Value = a.OrganizationId
WHERE a.AttendanceFlag = 1
AND a.MeetingDate >= @MeetingDate1
AND a.MeetingDate < DATEADD(d, 1, @MeetingDate2)

;WITH peopleorgs AS (
    SELECT DISTINCT
        p.PeopleId
        ,p.Name
        ,p.Age
        ,o.OrganizationId
        ,Class = o.OrganizationName
        ,Dad = fa.Name
        ,Mom = mo.Name
    FROM #t a
    JOIN dbo.Organizations o ON o.OrganizationId = a.OrganizationId
    JOIN dbo.People p ON p.PeopleId = a.PeopleId
    JOIN dbo.Families f ON f.FamilyId = p.FamilyId
    LEFT JOIN dbo.OrganizationMembers om
        ON om.OrganizationId = o.OrganizationId AND om.PeopleId = p.PeopleId
    LEFT JOIN dbo.People fa
        ON fa.PeopleId IN (f.HeadOfHouseholdId, f.HeadOfHouseholdSpouseId)
        AND fa.GenderId = 1 AND p.PositionInFamilyId = 30
    LEFT JOIN dbo.People mo
        ON mo.PeopleId IN (f.HeadOfHouseholdId, f.HeadOfHouseholdSpouseId)
        AND mo.GenderId = 2 AND p.PositionInFamilyId = 30
    --WHERE om.MemberTypeId = 220 OR om.MemberTypeId IS NULL
)
SELECT linkfornext = '/Person2/' + CONVERT(VARCHAR, po.PeopleId)
       ,po.Name
       ,po.Age
       ,linkfornext = '/Org/' + CONVERT(VARCHAR, po.OrganizationId)
       ,po.Class
       ,linkfornext = '/TransactionHistory/'
            + CONVERT(VARCHAR, po.PeopleId) + '/' + CONVERT(VARCHAR, po.OrganizationId)
       ,[# Sundays] = ( SELECT COUNT(*) FROM (
                    SELECT Sunday
                    FROM #t
                    WHERE PeopleId = po.PeopleId
                    AND OrganizationId = po.OrganizationId
                    GROUP BY Sunday ) tt
                )
       ,[9:30] = (  SELECT COUNT(*)
                    FROM #t
                    WHERE PeopleId = po.PeopleId
                    AND OrganizationId = po.OrganizationId
                    AND CONVERT(TIME, MeetingDate) = '9:30'
                )
       ,[11:00] = (  SELECT COUNT(*)
                    FROM #t
                    WHERE PeopleId = po.PeopleId
                    AND OrganizationId = po.OrganizationId
                    AND CONVERT(TIME, MeetingDate) = '11:00'
                )
        ,po.Dad
        ,po.Mom
FROM peopleorgs po
--where po.Age <= 14
order by po.Class, po.Name

DROP TABLE #t

Latest Update

11/13/2020

Modify image link with secure protocol.