Visitor Attend Percent for Orgs
===============================

This SQL report is run from the blue Toolbar on the Organization > Search/Manage page.
Filter for the desired orgs and then you will be prompted to enter a date range.

The report will list all guests (both first-time and recent) to any org
within your search parameters.

The report sorts by orgname, then person name.
If **true** displays in the Partial Member column,
that means that the person was a visitor some of the time
and a member other times.
Since there is no way to know whether they were supposed to be there or not,
it assumes they were supposed to be there every week during the date range.

Create to SQL Report
--------------------
Follow the instructions in this help document to create the report.
See :doc:`../CreateSqlScript`,
using the code below for the script.

.. note::
    Be sure that run the report after saving it and then click `Add to Menu`
    if you want it available on the blue Toolbar on the Organization > Search/Manage page.
    The first time you run the report from Special Content,
    there will be no data.
    You run it here so that you can make it available on the toolbar.

.. figure:: https://i.tpsdb.com/2017-03-22_11-05-27.png
    :target: #

    Empty Report

.. code-block:: sql

    --class=StartEndReport
        SET @MeetingDate1 = IIF(DATEPART(WEEKDAY, @MeetingDate1) = 0, @MeetingDate1, dbo.SundayForDate(DATEADD(DAY, 7 , @MeetingDate1)))
        DECLARE @numweeks FLOAT = DATEDIFF(WEEK, @MeetingDate1, @MeetingDate2)

    ;WITH weekattends AS (
        SELECT PeopleId, OrganizationId, dbo.SundayForDate(MeetingDate) MeetingDay, IIF(COUNT(*) > 0, 1, 0) AttFlag
        FROM dbo.Attend
        WHERE CONVERT(DATE, MeetingDate) BETWEEN @MeetingDate1 AND @MeetingDate2
        AND AttendanceFlag = 1
        GROUP BY PeopleId, OrganizationId, dbo.SundayForDate(MeetingDate)
    )
    SELECT
         LinkForNext = '/Org/' + CONVERT(VARCHAR, a.OrganizationId)
        ,o.OrganizationName
        ,LinkForNext = '/Person2/' + CONVERT(VARCHAR, a.PeopleId)
        ,p.Name
        ,AttendPct = (COUNT(*) / @numweeks) * 100

        ,AttendStr = dbo.VisitAttendStr(a.OrganizationId, a.PeopleId, @MeetingDate1, @MeetingDate2)

        ,PartialMember = CONVERT(BIT, IIF(EXISTS(SELECT NULL
                FROM dbo.Attend
                WHERE OrganizationId = a.OrganizationId
                AND PeopleId = a.PeopleId
                AND AttendanceTypeId NOT IN (40, 50, 60)), 1, NULL))
    FROM weekattends a
    JOIN dbo.Organizations o ON o.OrganizationId = a.OrganizationId
    JOIN dbo.People p ON p.PeopleId = a.PeopleId
    WHERE a.OrganizationId IN (SELECT value FROM dbo.SplitInts(@orgids))
    AND MeetingDay BETWEEN @MeetingDate1 AND DATEADD(HOUR, 24, @MeetingDate2)
    AND EXISTS(SELECT NULL FROM dbo.Attend
        WHERE PeopleId = a.PeopleId
        AND OrganizationId = a.OrganizationId
        AND AttendanceTypeId IN (40,50,60))
    GROUP BY a.PeopleId, p.Name, a.OrganizationId, o.OrganizationName
    ORDER BY o.OrganizationName, p.Name

Run the report
--------------
Step 1
    Choose the orgs you want included by using any of the filters on the Organization > Search/Manage page
    and pressing Search.
Step 2
    Select `Visitor Attend Pct For Orgs` from the blue Toolbar under the code icon > Custom SQL Reports.
Step 3
    Enter Date Range (Sunday to Sunday).

|
|

+--------------------+------------------+
| **Latest Update**  | **11/13/2020**   |
+--------------------+------------------+

Modify image link with secure protocol.