Recent Visitors to Division
===========================

This report will look for all organizations within that Division and will list all the
New Guests, Recent Guests, and any Visiting Members who attended any of those orgs
within the past 90 days.

Run this Report
---------------
If you want to run the report from Special Content, you can enter the **Division ID#**
in the `Parameter` field, then click **Run Script**.

.. figure:: https://i.tpsdb.com/2017-10-25_12-58-59.png
    :target: #

Run Script from Special Content File

If you prefer, after you run the report the first time,
you can select `Add to Menu`.
This will add it to the **Reports in the main menu**.
When you run the report from there you will **add the Division ID# at the end of the URL**
and hit **Enter**.

The URL will look like this:

``mychurch.tpsdb.com/RunScript/RecentVisitorsToDivision/211``

In the URL, **mychurch** is your church name and
**211** is the ID of the Division in your database.

Regardless of how you run this report, you have the option to **Download as Excel**.

Create the Report
~~~~~~~~~~~~~~~~~
Copy the :ref:`code below <RecentVisitorsToDivisionCode>` and paste it
into a new SQL Script in your database. The recommended name is RecentVisitorsToDivision.

See :doc:`../CreateSqlScript`.


Sample Report
~~~~~~~~~~~~~
Notice that the report gives the PID# and the Org ID#, both of which are links.
The name of the class/org is listed and the name of the recent guest, the last attended date,
and how many times that person has visited in the past 90 days.

You can change the **lookback days** in the DECLARE statement from 90
to whatever number of days you prefer.
Also, the code below has the report **sorting by Org, then by Name**.
You can reverse that if you prefer to sort by Name first.

.. figure:: https://i.tpsdb.com/2017-10-25_10-59-36.png
    :target: #

.. _RecentVisitorsToDivisionCode:

.. code-block:: sql

    DECLARE @lookback INT = 90

    ;WITH recentvisitors AS (
        SELECT
            a.PeopleId,
            o.OrganizationId,
            LastDt = MAX(a.MeetingDate),
            NumVisits = COUNT(*)
        FROM dbo.Attend a
        JOIN dbo.Organizations o ON o.OrganizationId = a.OrganizationId
        WHERE AttendanceFlag = 1
        AND AttendanceTypeId IN (40,50,60)
        AND EXISTS (SELECT NULL
            FROM dbo.DivOrg dd
            WHERE dd.DivId = @p1
            AND dd.OrgId = o.OrganizationId
        )
        AND a.MeetingDate > DATEADD(DAY, -@lookback, GETDATE())
        GROUP BY a.PeopleId, o.OrganizationId
    )
    SELECT
        rv.PeopleId
        ,rv.OrganizationId
        ,p.Name2
        ,o.OrganizationName
        ,rv.LastDt
        ,rv.NumVisits
    FROM recentvisitors rv
    JOIN dbo.People p ON p.PeopleId = rv.PeopleId
    JOIN dbo.Organizations o ON o.OrganizationId = rv.OrganizationId
    ORDER BY o.OrganizationName, Name2

|
|

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

Modify image link with secure protocol.