Page Comparison

Actions

For Discussion 

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

...

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;

...

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 =IN ('Health Education England Yorkshire and the Humber',
									'Yorkshire & Humber Deanery (North East)' 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  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 Posts5,292 Current Posts, or the19,303 with a NULL Managing Deaneries;

Of the above 19,303 Current Posts with a NULL Managing Deanery, they have the following IsTrainingPost values;

select Status, p.IsTrainingPost, count(*) as rc
from [HEELIVE].[vwPost] as p
where status = 'Current'
and managingdeaneryletb IS NULL
group by Status, p.IsTrainingPost

Current Posts with a NULL Managing Deanery by IsTrainingPost

Current Non Training Posts will NULL ManagingDeanery

Current Non Training Posts will NULL ManagingDeanery by ApprovedGradeName

Current Posts with NULL managingdeaneryletb and IsTrainingPost in ('No','Unknown') group by nationalPostNumber, MainSiteName and LocalOfficeName

Current Posts and IsTrainingPost = 'No' and SUBSTRING(nationalPostNumber, 1,3) = 'RK9' and MainSiteName = 'Derriford Hospital'

Current Posts managingdeaneryletb IS NULL and IsTrainingPost = 'No' and SUBSTRING(nationalPostNumber, 1,3) = 'NTH' and MainSiteName = 'Freeman Hospital'

Current Posts managingdeaneryletb IS NULL and IsTrainingPost = 'No' and SUBSTRING(nationalPostNumber, 1,3) = 'NTH' and MainSiteName = 'The Royal Victoria Infirmary'

Current Posts managingdeaneryletb IS NULL and IsTrainingPost = 'No' and SUBSTRING(nationalPostNumber, 1,3) = 'NTH' order by AddedDate

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

...

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;

...

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;

...

The current sites with a NULL MainSiteID;

Posts with a NULL vwPost.MainSiteID value

...

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