SQL

Org Search Member Stats

This script runs from the OrgSearch BlueToolbar.

The intent of the report is to allow you to filter for a set of organizations and get a report of all current members in those orgs along with the following information related to that org and information related to the individual’s Main Fellowship org:

  • Org ID# (which is a link to the org)

  • Org Name

  • First Name of individual

  • Last Name of individual

  • Join Date (church membership)

  • Enroll Date (org enrollment)

  • Last Attended Date

  • Attendance Percentage

  • Attendance String

  • Member Type (in filtered Org)

  • Main Fellowship Org Name

  • Main Fellowship Leader

  • Main Fellowship Date Last Attended

  • Main Fellowship Attendance Percentate

  • Main Fellowship Attendance String

The reason we added the Main Fellowship information is to allow you to further evaluate a person’s involvement in your church.

Sample Report

https://i.tpsdb.com/2017-10-05_18-32-58.png

Use the following code to Create the SQL Script. See How to create a SQL Script.

Code

;With FellowshipInfo as (
        SELECT c.OrganizationId
                        ,c.OrganizationName
                        , c.FirstName
                        , c.LastName
                        , c.PeopleId
                        , c.JoinDate
                        , c.EnrollDate
                        , c.LastAttend
                        , c.AttendPct
                        , c.AttendStr
                        , c.MemberType
                        , p.BibleFellowshipClassId
        FROM dbo.CurrOrgMembers(@orgids) c
        JOIN People p on p.PeopleId = c.PeopleId
)
Select fi.OrganizationId
        , fi.OrganizationName
        , fi.FirstName
        , fi.LastName
        , fi.JoinDate
        , fi.EnrollDate
        , fi.LastAttend
        , fi.AttendPct
        , fi.AttendStr
        , fi.MemberType
        , MainFellowshipName = mf.OrganizationName
        , MFLeader = mf.LeaderName
        , MFLastAttend = mfm.LastAttended
        , MFAttPct = mfm.AttendPct
        , MFAttStr = mfm.AttendStr
FROM FellowshipInfo fi
JOIN Organizations mf on fi.BibleFellowshipClassId = mf.OrganizationId
JOIN OrganizationMembers mfm on mfm.PeopleId = fi.PeopleId and mfm.OrganizationId = fi.BibleFellowshipClassId
ORDER BY fi.OrganizationID, fi.LastName


Latest Update

11/13/2020

Modify image link with secure protocol.