DR v2 Programmes with No ManagingDeanery.xlsx

These are the 33 Current Programmes without a Managing Deanery value set;

...

TIS is using the [vwProgramme].ManagingDeanery field to determine the Programme Owner value in TIS, and will NOT be using the Hicom [vwLocalOffice] data.

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

For Discussion

#

Comment

Owner

1

Can HEYH resolve their current 33 programmes below without a ManagingDeanery value ?

Without this information, TIS will not been able to filter the Programme data correctly.

Alistair Pringle (Unlicensed)/Former user (Deleted)/Joanne Watson (Unlicensed)

2

What should we do with the 16,892 Inactive Programmes without a Managing Deanery ?

Alistair Pringle (Unlicensed)/Former user (Deleted)/Joanne Watson (Unlicensed)

...

DR v2 Programmes with No ManagingDeanery.xlsx

There are the 33 Current Programmes without a Managing Deanery value set;

LocalOfficeName	Status	RecordOrigin	Status	ManagingDeanery	ProgrammeName	ProgrammeNumber	ProgrammeID	LeadProviderTrustID	LeadProviderName	LeadProviderKnownAs	LeadProviderTrustNumber	ProgrammeDirectors	DateAdded	Curr_people	Inactive_people	Curr_posts
HEYH	Current	HEELIVE	Current	NULL	GP Retainer	YAHGPRET	3236655	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	27
HEYH	Current	HEELIVE	Current	NULL	Neurosurgery	YAH912/342	2251469	NULL	NULL	NULL	NULL	NULL	27/09/2017	30	0	29
HEYH	Current	HEELIVE	Current	NULL	Immunology	YAH880/318	4156483	NULL	NULL	NULL	NULL	NULL	27/09/2017	3	0	4
HEYH	Current	HEELIVE	Current	NULL	Vascular Surgery	YAH2405	5266349	NULL	NULL	NULL	NULL	NULL	27/09/2017	11	0	1
HEYH	Current	HEELIVE	Current	NULL	Child and Adolescent Psychiatry	YAH873/364	5125838	NULL	NULL	NULL	NULL	NULL	27/09/2017	17	2	18
HEYH	Current	HEELIVE	Current	NULL	Paediatric Surgery Consortium	EMD/NTH	7614239	NULL	NULL	NULL	NULL	NULL	27/09/2017	6	0	0
HEYH	Current	HEELIVE	Current	NULL	ACCS Anaesthetics	YAH2455	7653841	NULL	NULL	NULL	NULL	NULL	27/09/2017	93	0	223
HEYH	Current	HEELIVE	Current	NULL	Emergency Medicine DRE_EM	YAHDRE867/324	8585584	NULL	NULL	NULL	NULL	NULL	27/09/2017	19	0	29
HEYH	Current	HEELIVE	Current	NULL	Medical Virology	YAH2383	9564045	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	10
HEYH	Current	HEELIVE	Current	NULL	Paediatrics	YAH517/329	10271598	NULL	NULL	NULL	NULL	NULL	27/09/2017	423	4	394
HEYH	Current	HEELIVE	Current	NULL	Rehabilitation Medicine	YAH911/313	11808903	NULL	NULL	NULL	NULL	NULL	27/09/2017	10	0	11
HEYH	Current	HEELIVE	Current	NULL	Forensic Psychiatry	YAH362	12400083	NULL	NULL	NULL	NULL	NULL	27/09/2017	4	0	9
HEYH	Current	HEELIVE	Current	NULL	Respiratory Medicine	YAH870/715	14690954	NULL	NULL	NULL	NULL	NULL	27/09/2017	63	1	76
HEYH	Current	HEELIVE	Current	NULL	Medical Ophthalmology	YAH2378	11196934	NULL	NULL	NULL	NULL	NULL	27/09/2017	1	0	1
HEYH	Current	HEELIVE	Current	NULL	Public Health Medicine	YAH328	11462287	NULL	NULL	NULL	NULL	NULL	27/09/2017	38	0	46
HEYH	Current	HEELIVE	Current	NULL	Diagnostic Neuropathology	YAH2415	11074895	NULL	NULL	NULL	NULL	NULL	27/09/2017	1	0	1
HEYH	Current	HEELIVE	Current	NULL	ACCS General	YAHACS	14552775	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	0
HEYH	Current	HEELIVE	Current	NULL	Rheumatology	YAH865/347	18776978	NULL	NULL	NULL	NULL	NULL	27/09/2017	29	0	27
HEYH	Current	HEELIVE	Current	NULL	Occupational Medicine	YAH307	19936952	NULL	NULL	NULL	NULL	NULL	27/09/2017	4	0	5
HEYH	Current	HEELIVE	Current	NULL	Acute Care Common Stem	YAHACC	23152897	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	0
HEYH	Current	HEELIVE	Current	NULL	General Surgery	YAH878/321	17868406	NULL	NULL	NULL	NULL	NULL	27/09/2017	84	0	128
HEYH	Current	HEELIVE	Current	NULL	Sport and Exercise Medicine	YAH917/721	22190033	NULL	NULL	NULL	NULL	NULL	27/09/2017	4	0	5
HEYH	Current	HEELIVE	Current	NULL	Medical Psychotherapy	YAH879/365	26126466	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	2
HEYH	Current	HEELIVE	Current	NULL	Combined Infection Training	YAH2675	22384672	NULL	NULL	NULL	NULL	NULL	27/09/2017	18	0	30
HEYH	Current	HEELIVE	Current	NULL	Endocrinology and Diabetes Mellitus	YAH874/315	26949308	NULL	NULL	NULL	NULL	NULL	27/09/2017	36	0	47
HEYH	Current	HEELIVE	Current	NULL	Trauma and Orthopaedic Surgery	YAH872/339	27529605	NULL	NULL	NULL	NULL	NULL	27/09/2017	74	2	90
HEYH	Current	HEELIVE	Current	NULL	ACCS Emergency Medicine	YAH2438	27846357	NULL	NULL	NULL	NULL	NULL	27/09/2017	14	0	212
HEYH	Current	HEELIVE	Current	NULL	Broad Based Training	YAH2471	28946454	NULL	NULL	NULL	NULL	NULL	27/09/2017	1	0	12
HEYH	Current	HEELIVE	Current	NULL	Paediatric and perinatal pathology	YAH3992	30534660	NULL	NULL	NULL	NULL	NULL	27/09/2017	2	0	2
HEYH	Current	HEELIVE	Current	NULL	ACCS Intensive Care Medicine	YAHAIC	31235757	NULL	NULL	NULL	NULL	NULL	27/09/2017	0	0	0
HEYH	Current	HEELIVE	Current	NULL	Psychiatry of Learning Disability	YAH361	19856952	NULL	NULL	NULL	NULL	NULL	27/09/2017	6	0	7
HEYH	Current	HEELIVE	Current	NULL	Oral and Maxillo-Facial Surgery	YAH333	276055916	NULL	NULL	NULL	NULL	NULL	27/09/2017	13	0	16
HEYH	Current	HEELIVE	Current	NULL	Clinical Genetics	YAH875/310	276055924	NULL	NULL	NULL	NULL	NULL	27/09/2017	6	0	7

...

LocalOfficeName	ProgrammeNumber	ProgrammeName	ProgName
HEYH	CHESTERFIELD STGP 015	CHERSTERFIELD ST1 PROGRAMME 15	CHESTERFIELD
HEYH	Trent FP 047	047 - 2008 starters	Trent
HEYH	Trent FP 008	008 - 2009/10 starters	Trent
HEYH	Trent FP 090	090 - 2008/9/10 starters	Trent
HEYH	Trent FP 048	048 - 2008/9/10 starters	Trent
HEYH	Trent FP 010	010 - 2008/9/10 starters	Trent
HEYH	Trent FP SA2 - 2008	SA2 - 2008	Trent
HEYH	Trent FP 086	086 - 2008/9/10 starters	Trent
HEYH	Trent FP 029	029 - 2008/9/10 starters	Trent
HEYH	Trent FP 027	027 - 2008/9 starters	Trent
HEYH	Trent FP 068(Ac)	068(Ac) - 2009/10 starters	Trent
HEYH	Trent FP 002	002 - 2009/10 starters	Trent
HEYH	Trent FP 013	013 - 2008 starters	Trent
HEYH	Trent FP 056	056 - 2008/9 starters	Trent
HEYH	Trent FP 031	031 - 2008/9 starters	Trent
HEYH	Trent FP 036	036 - 2008/9 starters	Trent
HEYH	Rotation 49	NGH F1	Rotation

ManagingDeanery to LocalOffice Mapping

/9/10 starters	Trent
HEYH	Trent FP SA2 - 2008	SA2 - 2008	Trent
HEYH	Trent FP 086	086 - 2008/9/10 starters	Trent
HEYH	Trent FP 029	029 - 2008/9/10 starters	Trent
HEYH	Trent FP 027	027 - 2008/9 starters	Trent
HEYH	Trent FP 068(Ac)	068(Ac) - 2009/10 starters	Trent
HEYH	Trent FP 002	002 - 2009/10 starters	Trent
HEYH	Trent FP 013	013 - 2008 starters	Trent
HEYH	Trent FP 056	056 - 2008/9 starters	Trent
HEYH	Trent FP 031	031 - 2008/9 starters	Trent
HEYH	Trent FP 036	036 - 2008/9 starters	Trent
HEYH	Rotation 49	NGH F1	Rotation

...

ManagingDeanery to TIS LocalOffice Mapping

Using the following ManagingDeanery value to TIS LocalOffice Mapping;

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 = '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

Using the above logic, leaves the following Programmes unable to determine the TIS LocalOffice

status	LocalOfficeName	ManagingDeanery	mapped_lo	rc
Current	HEEOE	London	UNKNOWN	1
Current	HEWM	Defence Postgraduate Medical Deanery	UNKNOWN	1
Current	HEYH	NULL	UNKNOWN	33
Inactive	HEEM	NULL	UNKNOWN	363
Inactive	HELASE	NULL	UNKNOWN	15698
Inactive	HESW	NULL	UNKNOWN	637
Inactive	HEWTV	NULL	UNKNOWN	52
Inactive	HEYH	NULL	UNKNOWN	142

vwLocalOffice to vwProgramme MD.xlsx

Code Block

	sql
	sql

select p.status, lop.LocalOfficeName, p.ProgrammeNumber, p.ProgrammeName, p.ManagingDeanery, 
	   case when p.ManagingDeanery = 'Health Education England North West' THEN 'HENW'
			when p.ManagingDeanery = 'Health Education England West Midlands' THEN 'HEWM'
			when p.ManagingDeanery = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
			when p.ManagingDeanery = 'Health Education England North East' THEN 'HENE'
			when p.ManagingDeanery = 'Health Education England East of England' THEN 'HEEOE'
			when p.ManagingDeanery IN ('Health Education England South West', 
									   'Peninsula Local Office - HESW', 
									   'Severn Local Office - HESW') THEN 'HESW'
			when p.ManagingDeanery IN ('Health Education England East Midlands', 
									   'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
			when p.ManagingDeanery 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.ManagingDeanery IN ('Health Education England Wessex', 
                                       'Health Education England Thames Valley') THEN 'HEWTV'
			else 'UNKNOWN'
		end as mapped_lo
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lop on (lop.ItemType = 'Programme' and lop.UniqueID = p.programmeid)
--where status = 'Current'
--and managingdeanery is not null
order by 6,1,2 desc

select p.status, lop.LocalOfficeName, p.ManagingDeanery, 
	   case when p.ManagingDeanery = 'Health Education England North West' THEN 'HENW'
			when p.ManagingDeanery = 'Health Education England West Midlands' THEN 'HEWM'
			when p.ManagingDeanery = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
			when p.ManagingDeanery = 'Health Education England North East' THEN 'HENE'
			when p.ManagingDeanery = 'Health Education England East of England' THEN 'HEEOE'
			when p.ManagingDeanery IN ('Health Education England South West', 
									   'Peninsula Local Office - HESW', 
									   'Severn Local Office - HESW') THEN 'HESW'
			when p.ManagingDeanery IN ('Health Education England East Midlands', 
									   'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
			when p.ManagingDeanery 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.ManagingDeanery IN ('Health Education England Wessex', 
								 
                         'Health Education England South London') THEN 'HELASE' 			when p.ManagingDeanery IN ('Health Education England Wessex', 'Health Education England Thames Valley') THEN 'HEWTV'
			else 'UNKNOWN'
		end as mapped_lo, count(*) as rc
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lop on (lop.ItemType = 'Programme' and lop.UniqueID = p.programmeid)
--where status = 'Current'
--and managingdeanery is not null
--order by 6,1,2 desc
group by p.status, lop.LocalOfficeName, p.ManagingDeanery, 
	   case when p.ManagingDeanery = 'Health Education England North West' THEN 'HENW'
			when p.ManagingDeanery = 'Health Education England West Midlands' THEN 'HEWM'
			when p.ManagingDeanery = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
			when p.ManagingDeanery = 'Health Education England North East' THEN 'HENE'
			when p.ManagingDeanery = 'Health Education England East of England' THEN 'HEEOE'
			when p.ManagingDeanery IN ('Health Education England South West', 
									   'Peninsula Local Office - HESW', 
									   'Severn Local Office - HESW') THEN 'HESW'
			when p.ManagingDeanery IN ('Health Education England East Midlands', 
									   'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
			when p.ManagingDeanery 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.ManagingDeanery IN ('Health Education England Wessex', 
                                       'Health Education England Thames Valley') THEN 'HEWTV'
			else 'UNKNOWN'
		end

status	LocalOfficeName	ManagingDeanery	mapped_lo	rc
Current	HEEM	Health Education England East Midlands	HEEM	88
Current	HEEM	Health Education England Yorkshire and the Humber	HEYH	2
Current	HEEOE	Health Education England East of England	HEEOE	109
Current	HEEOE	London	UNKNOWN	1
Current	HELASE	Health Education England Kent, Surrey and Sussex	HELASE	57
Current	HELASE	Health Education England North West London	HELASE	46
Current	HELASE	Health Education England North Central and East London	HELASE	62
Current	HELASE	Health Education England South London	HELASE	64
Current	HELASE	Health Education England Wessex	HEWTV	5
Current	HELASE	London LETBs	HELASE	23
Current	HENE	Health Education England North East	HENE	94
Current	HENE	Health Education England Yorkshire and the Humber	HEYH	6
Current	HENW	Health Education England North West	HENW	197
Current	HESW	Health Education England South West	HESW	148
Current	HEWM	Defence Postgraduate Medical Deanery	UNKNOWN	1
Current	HEWM	Health Education England West Midlands	HEWM	165
Current	HEWTV	Health Education England Thames Valley	HEWTV	77
Current	HEWTV	Health Education England Wessex	HEWTV	74
Current	HEYH	NULL	UNKNOWN	33
Current	HEYH	Health Education England Yorkshire and the Humber	HEYH	75
Inactive	HEEM	NULL	UNKNOWN	363
Inactive	HEEM	Health Education England East Midlands	HEEM	4
Inactive	HEEM	Yorkshire & Humber Deanery (North East)	HEEM	1
Inactive	HEEOE	Health Education England East of England	HEEOE	1
Inactive	HELASE	NULL	UNKNOWN	15698
Inactive	HELASE	Health Education England Kent, Surrey and Sussex	HELASE	22
Inactive	HELASE	Health Education England North West London	HELASE	3
Inactive	HELASE	Health Education England North Central and East London	HELASE	3
Inactive	HELASE	Health Education England South London	HELASE	4
Inactive	HELASE	London LETBs	HELASE	82
Inactive	HENE	Health Education England North East	HENE	1
Inactive	HENW	Health Education England North West	HENW	7
Inactive	HESW	NULL	UNKNOWN	637
Inactive	HESW	Peninsula Local Office - HESW	HESW	1
Inactive	HESW	Severn Local Office - HESW	HESW	9
Inactive	HEWTV	NULL	UNKNOWN	52
Inactive	HEWTV	Health Education England Thames Valley	HEWTV	15
Inactive	HEWTV	Health Education England Wessex	HEWTV	12
Inactive	HEYH	NULL	UNKNOWN	142

Version	Old Version 8	New Version Current
Changes made by	Russell Steel (Unlicensed)	Russell Steel (Unlicensed)
Saved on	04 Oct 2017	11 Oct 2017

Versions Compared

Key

ManagingDeanery to LocalOffice Mapping

ManagingDeanery to TIS LocalOffice Mapping

Page Comparison

Versions Compared

Key

ManagingDeanery to LocalOffice Mapping

ManagingDeanery to TIS LocalOffice Mapping