People | Trainee to Local Offices

select pm.PersonID, p.ManagingDeanery as lo, ProgrammeStartDate sd, pm.ProgrammeEndDateWithPOG ed, 'prg' as rec_type
from [HEELIVE].[vwProgrammeMembership] as pm
join [HEELIVE].[vwProgramme] as p on (p.ProgrammeID = pm.ProgrammeID)
union all
select pl.PersonID, ISNULL(p.ManagingDeaneryLETB, t.DeaneryLETB) as lo, pl.StartDate sd, pl.EndDate ed, 'plc' as rec_type 
from [HEELIVE].[vwPlacement] as pl
join [HEELIVE].[vwPost] as p on (p.PostID = pl.PostID)
left join [HEELIVE].[vwSite] as s on (s.SiteID = p.MainSiteID)
left join [HEELIVE].[vwTrust] as t on (t.TrustID = s.TrustID)
union all
select ps.PersonID, t.DeaneryLETB as lo, cast(null as date) as sd, cast(null as date) as ed, 'asi' as rec_type
FROM [HEELIVE].[vwPersonAssociatedSite] as ps
JOIN [HEELIVE].[vwSite] as s on (s.SiteID = ps.SiteID)
JOIN [HEELIVE].[vwTrust] as t on (t.TrustID = s.TrustID)
GROUP BY ps.PersonID, t.DeaneryLETB
union all
select pt.PersonID, t.DeaneryLETB as lo, cast(null as date) as sd, cast(null as date) as ed, 'atr' as rec_type
FROM [HEELIVE].[vwPersonAssociatedTrust] as pt
JOIN [HEELIVE].[vwTrust] as t on (t.TrustID = pt.TrustID)
GROUP BY pt.PersonID, t.DeaneryLETB
union all
select lo.UniqueID as PersonID, LocalOfficeName as lo, cast(null as date) as sd, cast(null as date) as ed, 'hlo' as rec_type
from [HEELIVE].[vwLocalOffice] as lo
where lo.ItemType = 'Person'
and lo.LocalOfficeName is not null
group by lo.UniqueID, LocalOfficeName