Contacts in Groups
Difficulty: Intermediate
Description: Contacts who currently belong to a Group or list of Groups.
Page: Contacts
Features: Correlated Subquery
The criteria utilizes EXISTS to check for matching records. The Contact's Participant_Record field is inserted to make this a correlated subquery. GETDATE and ISNULL are used to check that the current date is between the active date range for the Group Participant.
To create a View that lists all the Contacts who are active participants in a particular Group, the following SQL may be used for criteria.
EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))The Group ID can be a single Group ID or a list of Group IDs like this:
IN (20, 359, 1002)To remove a single group, use NOT EXISTS instead of EXISTS:
NOT EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))Tip: Combine with other criteria using AND to further limit your list. Use NOT EXISTS to find Contacts not in specified Groups.
Participants In Groups
This View can easily be changed to work on the Participants page. The criteria looks like this:
EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Participants.Participant_ID
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))