View: dbo.Staff_View
View definition
create view dbo.Staff_View
as
select
staff_KEY = S.staff_KEY
, staff_id = S.staff_id
, staff_id_sortable = S.staff_id_sortable
, is_tax_form_preparer = S.is_tax_form_preparer
, preparer_tax_information_number = S.preparer_tax_information_number
, third_party_designee_pin = S.third_party_designee_pin
, description = S.description
, user_name = S.user_name
, first_name = S.first_name
, middle_name = S.middle_name
, last_name = S.last_name
, social_security_number = S.social_security_number
, photograph = S.photograph
, comments = coalesce(S.comments, N'')
, date_hired = S.date_hired
, date_left = S.date_left
, date_education_met = S.date_education_met
, date_exam_passed = S.date_exam_passed
, date_experience_met = S.date_experience_met
, date_certified = S.date_certified
, certification_number = S.certification_number
, staff_status_KEY = S.staff_status_KEY
, staff_type_KEY = S.staff_type_KEY
, name_suffix = S.name_suffix
, title = S.title
, staff_status__description = SS.description
, office_KEY = S.office_KEY
, office__office_id = O.office_id
, office__contact_KEY = O.contact_KEY
, supervisor__staff_KEY = S.supervisor__staff_KEY
, supervisor__staff__staff_id = S2.staff_id
, supervisor__staff__staff_status_KEY = S2.staff_status_KEY
, supervisor__staff__office_KEY = S2.office_KEY
, supervisor__staff__supervisor__staff_KEY = S2.supervisor__staff_KEY
, supervisor__staff__contact_KEY = S2.contact_KEY
, contact_KEY = S.contact_KEY
, contact__primary__contact_phone_type_KEY = CT.primary__contact_phone_type_KEY
, contact__primary__contact_address_type_KEY = CT.primary__contact_address_type_KEY
, contact__mailing__contact_address_type_KEY = CT.mailing__contact_address_type_KEY
, contact__contact_type_KEY = CT.contact_type_KEY
, staff__active_status_KEY = S.staff_status_KEY -- Column added to match with the Active_Status View for Status Filter
from
dbo.Staff S
left outer join
dbo.Staff_Status SS on SS.staff_status_KEY = S.staff_status_KEY
left outer join
dbo.Office O on O.office_KEY = S.office_KEY
left outer join
dbo.Staff S2 on S2.staff_KEY = S.supervisor__staff_KEY
left outer join
dbo.Contact CT on CT.contact_KEY = S.contact_KEY