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.

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

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 code below and paste it into a new SQL Script in your database. The recommended name is RecentVisitorsToDivision.

See How to create a SQL Script.

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.

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

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.