Post Managing Deanery to TIS LocalOffice

For Discussion

#	Comment	Owner
1	How should the NULL MainSiteID post rows be associated with a LocalOfifce
2

TIS is using the [vwPost].ManagingDeanery field to determine the LocalOffice value in TIS, and will NOT be using the Hicom [vwLocalOffice] data derived via the

vwLocalOffice table using ItemType = 'Site' and UniqueID = MainSiteID

The data reported below has been taken from the Consolidated DR v1 on 09 Oct 2017

Posts to ManagingDeanery by Status

There are 19,303 Current Post rows where the ManagingDeaneryLETB field is NOT populated

Posts to ManagingDeanery by Status

Posts to ManagingDeanery and LocalOffice by Status

Of these 19,303 Posts, the NULL ManagingDeanery are assigned to the following LocalOffices;

LocalOfficeName	Status	managingdeaneryletb	rc
HELASE	Current	NULL	11700
HESW	Current	NULL	5874
HENE	Current	NULL	4961
HEEM	Current	NULL	4228
HEWTV	Current	NULL	3482
HEYH	Current	NULL	2297
HEEOE	Current	NULL	382
HENW	Current	NULL	300
HEWM	Current	NULL	273
NULL	Current	NULL	8

Posts to ManagingDeanery and LocalOffice by Status

Posts to ManagingDeanery and Mapped LocalOffice by Status

Using the same ManagingDeanery mapping logic as for Programmes

case when p.managingdeaneryletb = 'Health Education England North West' THEN 'HENW'
	when p.managingdeaneryletb = 'Health Education England West Midlands' THEN 'HEWM'
	when p.managingdeaneryletb = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
	when p.managingdeaneryletb = 'Health Education England North East' THEN 'HENE'
	when p.managingdeaneryletb = 'Health Education England East of England' THEN 'HEEOE'
	when p.managingdeaneryletb IN ('Health Education England South West',
							   'Peninsula Local Office - HESW',
							   'Severn Local Office - HESW') THEN 'HESW'
	when p.managingdeaneryletb IN ('Health Education England East Midlands',
							   'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
	when p.managingdeaneryletb IN ('Health Education England Kent, Surrey and Sussex',
							   'London LETBs',
							   'Health Education England North West London',
							   'Health Education England North Central and East London',
							   'Health Education England South London') THEN 'HELASE'
	when p.managingdeaneryletb IN ('Health Education England Wessex', 
                                   'Health Education England Thames Valley') THEN 'HEWTV'
	else 'UNKNOWN'
end as mapped_lo

The above case statement doesn't cater for the following 9 Managing Deaneries that are assigned to 588 Current Posts;

Status	managingdeaneryletb	mapped_lo	rc
Current	NULL	UNKNOWN	19303
Current	Health Education England North?Central and East London	UNKNOWN	5179
Current	Defence Postgraduate Medical Deanery	UNKNOWN	47
Current	South West Peninsula Deanery	UNKNOWN	33
Current	Wales Deanery	UNKNOWN	14
Current	Yorkshire & Humber Deanery (West)	UNKNOWN	9
Current	East Midlands HWD (North)	UNKNOWN	5
Current	Non NHS Authority	UNKNOWN	2
Current	Yorkshire & Humber Deanery (South)	UNKNOWN	2
Current	London Deanery	UNKNOWN	1

The above case statement doesn't cater for the following 17 Managing Deaneries that are assigned to 9,256 Inactive Posts;

Status	managingdeaneryletb	mapped_lo	rc
Inactive	NULL	UNKNOWN	59557
Inactive	London Deanery	UNKNOWN	6559
Inactive	Health Education England North?Central and East London	UNKNOWN	1753
Inactive	North Thames Deanery	UNKNOWN	552
Inactive	South Thames Deanery	UNKNOWN	161
Inactive	Non NHS Authority	UNKNOWN	83
Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	49
Inactive	Non London LETB	UNKNOWN	33
Inactive	Oxford Deanery	UNKNOWN	21
Inactive	Yorkshire & Humber Deanery (West)	UNKNOWN	10
Inactive	North West Thames Deanery	UNKNOWN	9
Inactive	Yorkshire & Humber Deanery (South)	UNKNOWN	8
Inactive	Wales Deanery	UNKNOWN	6
Inactive	Overseas	UNKNOWN	5
Inactive	East Midlands HWD (South)	UNKNOWN	3
Inactive	South West Peninsula Deanery	UNKNOWN	2
Inactive	Northern Deanery	UNKNOWN	1
Inactive	West Midlands Deanery	UNKNOWN	1

Posts to ManagingDeanery and Mapped LocalOffice by Status

Posts to ManagingDeanery, LocalOffice and Mapped LocalOffice by Status

Breaking down the Current mapped UNKOWN LocalOffice values further using the LocalOffice;

LocalOfficeName	Status	managingdeaneryletb	mapped_lo	rc
HEEM	Current	NULL	UNKNOWN	4228
HEEM	Current	Health Education England North?Central and East London	UNKNOWN	259
HEEM	Current	South West Peninsula Deanery	UNKNOWN	5
HEEM	Current	East Midlands HWD (North)	UNKNOWN	5
HEEM	Current	Yorkshire & Humber Deanery (South)	UNKNOWN	1
HEEOE	Current	Health Education England North?Central and East London	UNKNOWN	1517
HEEOE	Current	NULL	UNKNOWN	382
HEEOE	Current	East Midlands HWD (North)	UNKNOWN	5
HEEOE	Current	Defence Postgraduate Medical Deanery	UNKNOWN	4
HEEOE	Current	South West Peninsula Deanery	UNKNOWN	1
HELASE	Current	NULL	UNKNOWN	11700
HELASE	Current	Health Education England North?Central and East London	UNKNOWN	5167
HELASE	Current	Defence Postgraduate Medical Deanery	UNKNOWN	46
HELASE	Current	South West Peninsula Deanery	UNKNOWN	21
HELASE	Current	Wales Deanery	UNKNOWN	14
HELASE	Current	East Midlands HWD (North)	UNKNOWN	5
HELASE	Current	Non NHS Authority	UNKNOWN	2
HELASE	Current	Yorkshire & Humber Deanery (West)	UNKNOWN	1
HELASE	Current	London Deanery	UNKNOWN	1
HENE	Current	NULL	UNKNOWN	4961
HENW	Current	NULL	UNKNOWN	300
HENW	Current	Wales Deanery	UNKNOWN	12
HENW	Current	Defence Postgraduate Medical Deanery	UNKNOWN	1
HENW	Current	Health Education England North?Central and East London	UNKNOWN	1
HESW	Current	NULL	UNKNOWN	5874
HESW	Current	Health Education England North?Central and East London	UNKNOWN	111
HESW	Current	South West Peninsula Deanery	UNKNOWN	33
HESW	Current	East Midlands HWD (North)	UNKNOWN	5
HESW	Current	Wales Deanery	UNKNOWN	2
HESW	Current	Defence Postgraduate Medical Deanery	UNKNOWN	1
HEWM	Current	NULL	UNKNOWN	273
HEWM	Current	Health Education England North?Central and East London	UNKNOWN	112
HEWM	Current	East Midlands HWD (North)	UNKNOWN	5
HEWM	Current	Defence Postgraduate Medical Deanery	UNKNOWN	2
HEWM	Current	South West Peninsula Deanery	UNKNOWN	1
HEWTV	Current	NULL	UNKNOWN	3482
HEWTV	Current	South West Peninsula Deanery	UNKNOWN	22
HEWTV	Current	Health Education England North?Central and East London	UNKNOWN	5
HEYH	Current	NULL	UNKNOWN	2297
HEYH	Current	Yorkshire & Humber Deanery (West)	UNKNOWN	9
HEYH	Current	Yorkshire & Humber Deanery (South)	UNKNOWN	2
NULL	Current	NULL	UNKNOWN	8

And of the Inactive ones;

LocalOfficeName	Status	managingdeaneryletb	mapped_lo	rc
HEEM	Inactive	NULL	UNKNOWN	15956
HEEM	Inactive	London Deanery	UNKNOWN	59
HEEM	Inactive	Health Education England North?Central and East London	UNKNOWN	30
HEEM	Inactive	Yorkshire & Humber Deanery (South)	UNKNOWN	7
HEEM	Inactive	North Thames Deanery	UNKNOWN	7
HEEM	Inactive	North West Thames Deanery	UNKNOWN	5
HEEM	Inactive	East Midlands HWD (South)	UNKNOWN	3
HEEM	Inactive	South West Peninsula Deanery	UNKNOWN	1
HEEM	Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	1
HEEM	Inactive	Non London LETB	UNKNOWN	1
HEEOE	Inactive	NULL	UNKNOWN	4352
HEEOE	Inactive	Health Education England North?Central and East London	UNKNOWN	217
HEEOE	Inactive	London Deanery	UNKNOWN	175
HEEOE	Inactive	North Thames Deanery	UNKNOWN	80
HEEOE	Inactive	North West Thames Deanery	UNKNOWN	5
HEEOE	Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	2
HELASE	Inactive	NULL	UNKNOWN	25177
HELASE	Inactive	London Deanery	UNKNOWN	6206
HELASE	Inactive	Health Education England North?Central and East London	UNKNOWN	1747
HELASE	Inactive	North Thames Deanery	UNKNOWN	538
HELASE	Inactive	South Thames Deanery	UNKNOWN	160
HELASE	Inactive	Non NHS Authority	UNKNOWN	83
HELASE	Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	49
HELASE	Inactive	Non London LETB	UNKNOWN	33
HELASE	Inactive	Oxford Deanery	UNKNOWN	17
HELASE	Inactive	North West Thames Deanery	UNKNOWN	9
HELASE	Inactive	Wales Deanery	UNKNOWN	6
HELASE	Inactive	Overseas	UNKNOWN	5
HELASE	Inactive	Northern Deanery	UNKNOWN	1
HELASE	Inactive	South West Peninsula Deanery	UNKNOWN	1
HELASE	Inactive	Yorkshire & Humber Deanery (West)	UNKNOWN	1
HELASE	Inactive	West Midlands Deanery	UNKNOWN	1
HENE	Inactive	NULL	UNKNOWN	7829
HENE	Inactive	Northern Deanery	UNKNOWN	1
HENW	Inactive	NULL	UNKNOWN	592
HENW	Inactive	Wales Deanery	UNKNOWN	6
HENW	Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	1
HESW	Inactive	NULL	UNKNOWN	12670
HESW	Inactive	Health Education England North?Central and East London	UNKNOWN	8
HESW	Inactive	North West Thames Deanery	UNKNOWN	5
HESW	Inactive	South Thames Deanery	UNKNOWN	4
HESW	Inactive	South West Peninsula Deanery	UNKNOWN	2
HEWM	Inactive	NULL	UNKNOWN	669
HEWM	Inactive	Health Education England North?Central and East London	UNKNOWN	7
HEWM	Inactive	North West Thames Deanery	UNKNOWN	5
HEWM	Inactive	Defence Postgraduate Medical Deanery	UNKNOWN	2
HEWTV	Inactive	NULL	UNKNOWN	14619
HEWTV	Inactive	London Deanery	UNKNOWN	125
HEWTV	Inactive	Oxford Deanery	UNKNOWN	3
HEWTV	Inactive	South Thames Deanery	UNKNOWN	2
HEWTV	Inactive	South West Peninsula Deanery	UNKNOWN	1
HEWTV	Inactive	Health Education England North?Central and East London	UNKNOWN	1
HEYH	Inactive	NULL	UNKNOWN	15061
HEYH	Inactive	Yorkshire & Humber Deanery (West)	UNKNOWN	10
HEYH	Inactive	Yorkshire & Humber Deanery (South)	UNKNOWN	8
HEYH	Inactive	Non NHS Authority	UNKNOWN	5
HEYH	Inactive	East Midlands HWD (South)	UNKNOWN	3
HEYH	Inactive	Health Education England North?Central and East London	UNKNOWN	1
NULL	Inactive	NULL	UNKNOWN	3631
NULL	Inactive	London Deanery	UNKNOWN	353
NULL	Inactive	North Thames Deanery	UNKNOWN	14
NULL	Inactive	Health Education England North?Central and East London	UNKNOWN	6
NULL	Inactive	Oxford Deanery	UNKNOWN	4
NULL	Inactive	South Thames Deanery	UNKNOWN	1

Posts to ManagingDeanery, LocalOffice and Mapped LocalOffice by Status

Posts with a NULL vwPost.MainSiteID value

How to identify the LocalOffice for Posts with a NULL MainSiteID field;

select ISNULL(lo.LocalOfficeName, 'NULL') LocalOfficeName_MS, Status, p.managingdeaneryletb, 
		case when p.managingdeaneryletb = 'Health Education England North West' THEN 'HENW'
            when p.managingdeaneryletb = 'Health Education England West Midlands' THEN 'HEWM'
            when p.managingdeaneryletb = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
            when p.managingdeaneryletb = 'Health Education England North East' THEN 'HENE'
            when p.managingdeaneryletb = 'Health Education England East of England' THEN 'HEEOE'
            when p.managingdeaneryletb IN ('Health Education England South West',
                                       'Peninsula Local Office - HESW',
                                       'Severn Local Office - HESW') THEN 'HESW'
            when p.managingdeaneryletb IN ('Health Education England East Midlands',
                                       'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
            when p.managingdeaneryletb IN ('Health Education England Kent, Surrey and Sussex',
                                       'London LETBs',
                                       'Health Education England North West London',
                                       'Health Education England North Central and East London',
                                       'Health Education England South London') THEN 'HELASE'
            when p.managingdeaneryletb IN ('Health Education England Wessex', 'Health Education England Thames Valley') THEN 'HEWTV'
            else 'UNKNOWN'
        end as mapped_lo,  FundingBodyTrustID, ISNULL(lot.LocalOfficeName, 'NULL') LocalOfficeName_Trust, substring(NationalPostNumber,1,3) as ntn_lo, count(*) as rc
from [HEELIVE].[vwPost] as p
left join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Site' and lo.UniqueID = p.MainSiteID)
left join [HEELIVE].[vwLocalOffice] as lot on (lot.ItemType = 'Trust' and lot.UniqueID = p.FundingBodyTrustID)
where lo.LocalOfficeName is null
group by lo.LocalOfficeName, Status, p.managingdeaneryletb, MainSiteID, FundingBodyTrustID, lot.LocalOfficeName, substring(NationalPostNumber,1,3)
order by 2, 4 desc, 5, 8 desc,1;

The current sites with a NULL MainSiteID;

LocalOfficeName_MS	Status	managingdeaneryletb	mapped_lo	FundingBodyTrustID	LocalOfficeName_Trust	ntn_lo	rc
NULL	Current	NULL	UNKNOWN	NULL	NULL	NULL	5
NULL	Current	NULL	UNKNOWN	NULL	NULL	NA	1
NULL	Current	NULL	UNKNOWN	129200612	HELASE	KSS	2
NULL	Current	Health Education England West Midlands	HEWM	8140607	HENW	WMD	1
NULL	Current	Health Education England West Midlands	HEWM	8140607	HEEM	WMD	1
NULL	Current	Health Education England West Midlands	HEWM	8140607	HEEOE	WMD	1
NULL	Current	Health Education England West Midlands	HEWM	8140607	HELASE	WMD	1
NULL	Current	Health Education England West Midlands	HEWM	8140607	HEWM	WMD	1
NULL	Current	Severn Local Office - HESW	HESW	NULL	NULL	NULL	1
NULL	Current	Health Education England North West	HENW	6500118	HELASE	NWN	2
NULL	Current	Health Education England North West	HENW	6500118	HENW	NWN	2
NULL	Current	Health Education England North West	HENW	8139427	HENW	NWN	3
NULL	Current	Health Education England North West	HENW	8139859	HENW	NWN	3
NULL	Current	Health Education England North West	HENW	8140080	HENW	NWN	8
NULL	Current	Health Education England North West	HENW	8140588	HENW	NWN	3
NULL	Current	Health Education England North East	HENE	NULL	NULL	NTH	1

Posts with a NULL vwPost.MainSiteID value

Trusts with multiple LocalOffices assigned at the Trust Level;

select * from [HEELIVE].[vwTrust] where TrustID in (8140607,6500118)

RecordOrigin	STATUS	TrustName	TrustKnownAs	TrustNumber	DeaneryLETB	TrustID
HEELIVE	Current	Central Manchester University Hospitals NHS Foundation Trust	Central Manchester University Hospitals NHS Foundation Trust	RW3	North Western	6500118
HEELIVE	Current	St Helens and Knowsley Hospital Services NHS Trust	St Helens and Knowsley Hospital Services NHS Trust	RBN	Mersey	8140607