This View shows small group participants who are not serving on a ministry team.
Difficulty: Advanced
Purpose: Creates list of small group participants who are not involved with a ministry team.
Page: Participants
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]
The values highlighted below must be replaced 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