Group Participants in Default Participant Selection

To create a View that lists all the Group Participants in a selection, the following SQL may be used for the criteria:

Group_Participants.Participant_ID IN(SELECT SR.Record_ID FROM dp_Selections S JOIN dp_Selected_Records SR    ON S.Selection_ID = SR.Selection_ID    WHERE User_ID = dp_UserID AND Selection_Name = 'dp_Default' AND Page_ID =              (SELECT TOP 1 P.Page_ID FROM dp_Pages P        WHERE P.Table_Name = 'Participants' AND P.Filter_Clause IS NULL))

How to Use the View

Create a Selection of Participants. Navigate to Group Participants, and select this View from the list.

Limits

The Selection must be the default selection on Participants created by the User who is logged in.

Variations

Named Selections

For use with other Selections, you can change dp_Default to the name of the selection. Make sure it is in single quotes:

Selection_Name = 'dp_Default'

To use a list of named selections (the View will show all records matching all the Selections), use the IN comparison:

Selection_Name IN ('Name 1', 'Name 2', 'Name 3')

Other User Selections

If a User other than the logged in User will generate the Selection, you can change dp_UserID to the ID of the User who will maintain the Selection:

User_ID = 108

Active Groups and Group Participants

This variation uses GETDATE to limit the list to a current Group and current Participant:

Group_Participants.Participant_ID IN (SELECT SR.Record_ID FROM dp_Selections S JOIN dp_Selected_Records SR 	ON S.Selection_ID = SR.Selection_ID 	WHERE User_ID = dp_UserID AND Selection_Name = 'dp_Default' AND Page_ID =          	(SELECT TOP 1 P.Page_ID FROM dp_Pages P 		WHERE P.Table_Name = 'Participants' AND P.Filter_Clause IS NULL))AND Group_Participants.Group_ID IN (SELECT G.Group_ID FROM Groups G 	WHERE GETDATE() BETWEEN G.Start_Date AND ISNULL(G.End_Date,GETDATE()+1))AND GETDATE() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GETDATE()+1)

Techniques

• GETDATE SQL Function
• ISNULL SQL Function
• Subqueries
• ID IN SELECT