The purpose of this view example is to help churches identify any Company Users records with Security Roles in their systems (which are not recommended) and to display relevant information related to those records, like last login date.   

This View shows all Company Users records with Security Roles counts to all applications as well as the Platform specifically.  

Columns (View Field List)

This field shows the following columns: User ID, Company Name, Total Role Count, Platform Role Count, Most Recent Login, and Total Logins.

dp_Users.[User_ID] AS [User ID]
, Contact_ID_Table.[Company_Name] AS [Company Name]
, (SELECT COUNT(*) FROM dp_User_Roles UR 
WHERE UR.[User_ID] = dp_Users.[User_ID]) AS [Total Role Count]
, (SELECT COUNT(*) FROM dp_User_Roles UR 
WHERE UR.[User_ID] = dp_Users.[User_ID] 
AND (EXISTS(SELECT 1 FROM dp_Role_Pages RP
WHERE RP.[Role_ID] = UR.[Role_ID]
AND RP.[Access_Level] IS NOT NULL)
OR EXISTS(SELECT 1 FROM dp_Role_Sub_Pages RSP
WHERE RSP.[Role_ID] = UR.[Role_ID]
AND RSP.[Access_Level] IS NOT NULL))) AS [Platform Role Count]
, (SELECT TOP 1 AL.[Date_Time] FROM dp_Authentication_Log AL
WHERE AL.[User_ID] = dp_Users.[User_ID]
ORDER BY AL.[Date_Time] DESC) AS [Most Recent Login]
, (SELECT COUNT(*) FROM dp_Authentication_Log AL
WHERE AL.[User_ID] = dp_Users.[User_ID]) AS [Total Logins]

Criteria (View Clause)

This criteria limits the results to only Company User records with Security Roles

Contact_ID_Table.[Company] = 1
AND (SELECT COUNT(*) FROM dp_User_Roles UR
WHERE UR.[User_ID] = dp_Users.[User_ID]
AND (EXISTS(SELECT 1 FROM dp_Role_Pages RP
WHERE RP.[Role_ID] = UR.[Role_ID]
AND RP.[Access_Level] IS NOT NULL)
OR EXISTS(SELECT 1 FROM dp_Role_Sub_Pages RSP
WHERE RSP.[Role_ID] = UR.[Role_ID]
AND RSP.[Access_Level] IS NOT NULL))) > 0

Techniques

This View uses the following SQL Functions and techniques:

• EXISTS
• COUNT
• SELECT DISTINCT