Small Group Participants Not Serving on a Ministry Team
This view shows small group participants who are not serving on a ministry team.
Attention: This View example is offered as is and may need to be altered to fit your specific needs. For assistance, contact your SPoC; 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