Events Needing Approval
This View displays Events with Rooms, Services or Equipment that still need to be approved.
Difficulty: Advanced
Purpose: To show how many Rooms, Services, or Equipment associated with an event still need approval.
Page: Events
Fields
Standard Fields
The following are standard fields you may want to include. Together with the calculated fields, these can create a well-rounded view of Events and where they are in the approval process:
Events.[Event_Start_Date] AS [Event Start Date] , Events.[Event_Title] AS [Event Title] , Event_Type_ID_Table.[Event_Type] AS [Event Type] , Congregation_ID_Table.[Congregation_Name] AS [Campus] , Primary_Contact_Table.[Display_Name] AS [Contact] , Program_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] , Location_ID_Table.[Location_Name] AS [Location Name] , Program_ID_Table.[Program_Name] AS [Program Name] ,Events.[_Approved] AS [Approved]Counts of Unapproved Items
The following three fields will display the counts of Services, Rooms and Equipment which are not approved:
, (SELECT COUNT(ER.Event_Room_ID) FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ISNULL(ER._Approved,0)=0 ) AS [Rooms Not Approved] , (SELECT COUNT(ES.Event_Service_ID) FROM Event_Services ES WHERE ES.Event_ID = Events.Event_ID AND ISNULL(ES._Approved,0)=0 ) AS [Services Not Approved] , (SELECT COUNT(EE.Event_Equipment_ID) FROM Event_Equipment EE WHERE EE.Event_ID = Events.Event_ID AND ISNULL(EE._Approved,0)=0 ) AS [Equipment Not Approved]True or False for Approvals Needed
These fields are similar to the Counts (in the previous group of fields) but shows TRUE or FALSE rather than a count:
, (SELECT CASE WHEN 0 = COUNT(ER.Event_Room_ID) THEN 'False' ELSE 'True' END FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ISNULL(ER._Approved,0)=0 ) AS [Room Approval Needed] , (SELECT CASE WHEN 0 = COUNT(ES.Event_Service_ID) THEN 'False' ELSE 'True' END FROM Event_Services ES WHERE ES.Event_ID = Events.Event_ID AND ISNULL(ES._Approved,0)=0 ) AS [Service Approval Needed] , (SELECT CASE WHEN 0 = COUNT(EE.Event_Equipment_ID) THEN 'False' ELSE 'True' END FROM Event_Equipment EE WHERE EE.Event_ID = Events.Event_ID AND ISNULL(EE._Approved,0)=0 ) AS [Equipment Approval Needed]True or False For Any Approvals Needed
, (SELECT CASE WHEN 0 = (COUNT(ER.Event_Room_ID) + COUNT(ES.Event_Service_ID) + COUNT(EE.Event_Equipment_ID)) THEN 'False' ELSE 'True' END FROM Events E LEFT JOIN Event_Rooms ER ON E.Event_ID = ER.Event_ID LEFT JOIN Event_Services ES ON E.Event_ID = ES.Event_ID LEFT JOIN Event_Equipment EE ON E.Event_ID = EE.Event_ID WHERE E.Event_ID = Events.Event_ID AND ( ISNULL(ER._Approved,0)=0 OR ISNULL(ES._Approved,0)=0 OR ISNULL(EE._Approved,0)=0) ) AS [Approvals Needed]View Clause
The following clause will filter out any Events that do not need approval:
( Events.[Event_Start_Date] >= GetDate() AND ISNULL(Events.[Cancelled], 0) = 0 ) AND ( ISNULL(Events.[_Approved], 0) = 0 OR EXISTS (SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ISNULL(ER._Approved,0)=0) OR EXISTS (SELECT 1 FROM Event_Services ES WHERE ES.Event_ID = Events.Event_ID AND ISNULL(ES._Approved,0)=0) OR EXISTS (SELECT 1 FROM Event_Equipment EE WHERE EE.Event_ID = Events.Event_ID AND ISNULL(EE._Approved,0)=0) )Techniques
The following SQL Functions were used in this example and variations:
- COUNT
- EXISTS