This View displays Events with Rooms, Services, or Equipment that still need to be approved.

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

The following combines all approvals together and displays TRUE if there are Approvals Needed or FALSE if there is not:

, (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