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``

.. figure:: https://i.tpsdb.com/2016-06-29_11-29-15.png
    :target: #

    Sample Report

Use the following code to Create the SQL Script.
See :doc:`../CreateSqlScript`.

.. code-block:: sql

    --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.