Small Group Participants Not Serving on a Ministry Team
This view shows small group participants who are not serving on a ministry team.
CAUTION: Use this View Example at your own risk. It's offered as is and many need to be altered to fit your specific church culture. For assistance, contact your SPoC, or, if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).
Difficulty: Advanced
Purpose: Creates list of small group participants who are not involved with a ministry team.
Page: Participants
Field List
Contact_ID_Table.[Last_Name] AS [Last Name], Contact_ID_Table.[First_Name] AS [First Name], Contact_ID_Table.[Email_Address] AS [Email Address], Contact_ID_Table.[Mobile_Phone] AS [Mobile Phone], Contact_ID_Table_Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]View Clause
Replace the values with the IDs you are filtering for:
Contact_ID_Table_Household_Position_ID_Table.[Household_Position]='Head of Household'
AND Contact_ID_Table_Household_ID_Table_Congregation_ID_Table.[Congregation_ID] = 1
AND Exists(SELECT Group_Participant_ID FROM Group_Participants GP
JOIN GROUPS G ON G.Group_ID=GP.Group_ID
WHERE GP.Participant_ID=Participants.Participant_ID
AND G.Group_Type_ID = 1 AND ISNULL(GP.End_Date,GETDATE() +1)>GETDATE())
AND NOT EXISTS (SELECT Group_Role_Type_ID FROM Group_Roles GR
JOIN Group_Participants GP ON GP.Group_Role_ID=GR.Group_Role_ID
JOIN Groups G ON G.Group_ID=GP.Group_ID
WHERE Participants.Participant_ID=GP.Participant_ID
AND GR.Group_Role_Type_ID IN (1,3)
AND G.Ministry_ID IN (22,29)
AND ISNULL(GP.End_Date,GETDATE() +1)>GETDATE())Techniques
- EXISTS SQL Function
- GETDATE SQL Function