Difficulty: Advanced
Description: Displays all current Group Members for the groups of which the user is a member.
Page: Group Participants

Field List

Group_ID_Table.[Group_Name] AS [Group Name]
, Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name]
, Group_Participants.[Start_Date] AS [Start Date]
, Group_Participants.[End_Date] AS [End Date]
, Participant_ID_Table.[Participant_ID] AS [Participant ID]

View Clause

GETDATE() <= ISNULL(Group_Participants.[End_Date], GETDATE())
AND Group_Participants.[Group_ID] IN 
(
    SELECT gp.Group_ID
    FROM Group_Participants GP
    INNER JOIN Participants P ON GP.Participant_ID = P.Participant_ID
    INNER JOIN Contacts C ON P.Contact_ID = C.Contact_ID
    WHERE C.user_account = dp_UserID AND GETDATE() <= ISNULL(GP.End_Date, GETDATE())
)

Techniques

• GETDATE SQL Function
• ISNULL SQL Function
• INNER JOIN