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

 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

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

• EXISTS SQL Function
• GETDATE SQL Function