Background Check Information
This view shows Group Participants and the background check information for their most recent Background Check.
Difficulty: Advanced
Purpose: Creates a Field with the Background Check submitted date and a field with the Background Check Returned date.
Page: Group Participants
Field List
Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name] , Group_ID_Table.[Group_Name] AS [Group Name], Group_ID_Table_Parent_Group_Table.[Group_Name] AS [Parent Group], Group_Participants.[Start_Date] AS [Joined Team] ,(SELECT TOP 1 Background_Check_Started FROM Background_Checks BC JOIN Participants P ON BC.Contact_ID = P.Contact_ID WHERE P.Participant_ID = Group_Participants.Participant_ID ORDER BY Background_Check_Started DESC) AS Check_Started,(SELECT TOP 1 Background_Check_Submitted FROM Background_Checks BC JOIN Participants P ON BC.Contact_ID = P.Contact_ID WHERE P.Participant_ID = Group_Participants.Participant_ID ORDER BY Background_Check_Started DESC) AS Check_Submitted,(SELECT TOP 1 Background_Check_Returned FROM Background_Checks BC JOIN Participants P ON BC.Contact_ID = P.Contact_ID WHERE P.Participant_ID = Group_Participants.Participant_ID ORDER BY Background_Check_Started DESC) AS Check_Returned,(SELECT TOP 1 All_Clear FROM Background_Checks BC JOIN Participants P ON BC.Contact_ID = P.Contact_ID WHERE P.Participant_ID = Group_Participants.Participant_ID ORDER BY Background_Check_Started DESC) AS All_Clear, Group_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] , Group_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] , Group_Role_ID_Table.[Role_Title] AS [Role Title]View Clause
To ensure the view only returns current Group Participants.
GetDate() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GetDate())Techniques
- SELECT TOP 1
- DATEDIFF SQL Function
- GETDATE SQL Function
- ISNULL SQL Function