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 How to create a SQL Script, using the code below for the script.
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.
--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).