View: dbo.System_Event_View | |||
View definition | |||
-- add new System_Event_View to system CREATE VIEW dbo.System_Event_View AS SELECT recorded_event_KEY = RE.recorded_event_KEY, -- event_date_time is used for displaying event_date_time = RE.create_date, -- event_date is a date only version event_date_time used by filters event_date = DATEADD(DAY, 0, DATEDIFF(DAY, 0, RE.create_date)), event_class_type_KEY = RE.event_class_type_KEY, event_type_KEY = RE.event_type_KEY, event_class_type__description = ECT.description, event_type__description = ET.description, client_KEY = REONC.client_KEY, -- could be null. client_id = coalesce(CREO.id, N''), client__description = coalesce(CREO.description, N''), staff_id = SREO.id, staff__description = SREO.description FROM dbo.Recorded_Event AS RE -- join in the description for the Event_Class_Type JOIN dbo.Event_Class_Type AS ECT on RE.event_class_type_KEY = ECT.event_class_type_KEY -- join in the description for the Event_Type JOIN dbo.Event_Type AS ET on RE.event_type_KEY = ET.event_type_KEY -- we need to get both staff and client Recorded_Event_Object's -- and primary keys for both staff and client -- get the associated client Recorded_Event_Objects -- Note that not all events will be associated with a client. LEFT JOIN dbo.Recorded_Event_Client_Recorded_Event_Object AS RECREO on RE.recorded_event_KEY = RECREO.recorded_event_KEY LEFT JOIN ( SELECT id, description, REO.recorded_event_object_KEY FROM dbo.Recorded_Event_Object AS REO ) AS CREO on RECREO.recorded_event_object_KEY = CREO.recorded_event_object_KEY LEFT JOIN Recorded_Event_Object_N_Client AS REONC on RECREO.recorded_event_object_KEY = REONC.recorded_event_object_KEY -- get the staff information JOIN dbo.Recorded_Event_Staff_Recorded_Event_Object AS RESREO on RE.recorded_event_KEY = RESREO.recorded_event_KEY JOIN ( SELECT id, description, REO.recorded_event_object_KEY FROM dbo.Recorded_Event_Object AS REO ) AS SREO on RESREO.recorded_event_object_KEY = SREO.recorded_event_object_KEY | |||