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.
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:
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.
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.
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
Modify image link with secure protocol.