====== pears.ContactEventDepartmentView ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Gives DepartmentID linked to a Contact Event record

===== Original SQL =====
<code sql>
create view "pears"."ContactEventDepartmentView"( "contacteventid",
  "departmentid","contactdate","staffid","classcode" ) as
  select distinct "c"."contacteventid","p"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    from "pears"."contactevent" as "c" key join("pears"."placement" as "p") where "c"."placementid" is null union all
  select distinct "c"."contacteventid","v"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    from "pears"."contactevent" as "c" key join("pears"."vacancy" as "v") where "c"."placementid" is null union all
  select distinct "c"."contacteventid","v"."departmentid","c"."contactdate","c"."staffid","c"."classcode"
    from "pears"."contactevent" as "c" key join("pears"."progress" as "p"),"pears"."vacancy" as "v" where "p"."vacancyid" = "v"."vacancyid" and "c"."placementid" is null and "c"."vacancyid" is null union all
  select distinct "c"."contacteventid","s"."defaultdepartid" as "departmentid","c"."contactdate","c"."staffid","c"."classcode"
    from "pears"."contactevent" as "c" key join("pears"."staff" as "s") where "c"."placementid" is null and "c"."vacancyid" is null and "c"."progressid" is null
go

COMMENT ON VIEW "pears"."ContactEventDepartmentView" IS 
	'Gives DepartmentID linked to a Contact Event record'
go

COMMENT TO PRESERVE FORMAT ON VIEW "pears"."ContactEventDepartmentView" IS 
{create view ContactEventDepartmentView 
 (contacteventid, departmentid, contactdate, staffid, classcode) 
 as select distinct c.contacteventid, p.departmentid, c.contactdate, c.staffid, c.classcode 
 from contactevent c key join ( placement p) where not c.placementid is not null     
 union all select distinct c.contacteventid,v.departmentid, c.contactdate, c.staffid,  c.classcode 
 from contactevent c key join ( vacancy v) where c.placementid is null 
 union all  select distinct c.contacteventid, v.departmentid, c.contactdate, c.staffid,  c.classcode  
 from contactevent c key join (progress p),vacancy v where p.vacancyid=v.vacancyid and  c.placementid is null and  c.vacancyid is null 
 union all  select distinct c.contacteventid, s.defaultdepartid as departmentid, c.contactdate, c.staffid, c.classcode 
 from contactevent c key join ( staff s) where c.placementid is  null and  c.vacancyid is  null and  c.progressid is null
}
</code>