What would you like to know more about?

Event Rooms

This View shows what Rooms are associated with each Event record.

Difficulty: Advanced

Purpose: Creates a field with all associated rooms, separated by commas.

Page: Events

Field List

Events.[Event_Start_Date] AS [Event Start Date]
, Events.[Event_Title] AS [Event Title]
, STUFF((SELECT ', ' + R.Room_Name FROM Rooms R JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID  
WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 FOR XML PATH('')), 1, 2, '') AS [Event Rooms]

View Clause

Events.[Event_Start_Date] >= GETDATE()

Filters for Events with Rooms

To only list Events that have rooms associated with them you can add this criteria:

AND EXISTS (SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0)

Filters for Events with Unapproved Rooms

To only list Events that have Unapproved Room Reservations, add this criteria:

AND EXISTS (SELECT 1 FROM Event_Rooms ER  WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 AND ISNULL(ER._Approved,0)=0 )

Filter for Approved Events

To only list Events that have been approved, add this criteria:

AND ISNULL(Events._Approved,0)=1

Filtered for Unapproved Room List

To return a list of Rooms that are not approved, you can alter the Event Rooms field from the base example.

, STUFF((SELECT ', ' + R.Room_Name FROM Rooms R JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID   WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0  AND ISNULL(ER._Approved,0)=0 FOR XML PATH('')), 1, 2, '') AS [Unapproved Rooms]

Techniques

  • STUFF SQL Function
  • EXISTS SQL Function
  • ISNULL SQL Function
  • GETDATE SQL Function