Page Comparison

Actions

Jira Legacy

server	System JIRA
columns	key,summary,type,updated,assignee,status
maximumIssues	20
jqlQuery	labels = Posts_data_quality
serverId	4c843cd5-e5a9-329d-ae88-66091fcfe3c7

For Discussion

#	Comment	Owner
1	How should the NULL MainSiteID post rows be associated with a LocalOfifce, using ManagingDeanery, Site, Trust or NationlaPostNumber prefix	Joanne Watson (Unlicensed)
2

...

Of the 19,303 Current Posts with a NULL ManagingDeanery, 436 have a IsTrainingPost = Unknown, 8,907 have a IsTrainingPost = Yes and the remaining 9,960 have a IsTrainingPost = No, can these Posts be ignored in TIS as they are Trust and not trainee posts.

Joanne Watson (Unlicensed)

TIS is using the [vwPost].ManagingDeanery field to determine the Post Owner 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;

...

Posts to ManagingDeanery and LocalOffice by Status

Posts to ManagingDeanery and Mapped LocalOffice by Status

Using the same ManagingDeanery mapping logic as for Programmes

Code Block

	sql
	sql

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     '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 5,292 Current Posts, and 19or the19,303 with a NULL Managing Deaneries;

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

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

Code Block

	sql
	sql

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