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.

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.

https://i.tpsdb.com/2017-03-22_11-05-27.png — Empty Report¶

--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.