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