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

There are a further 16,892 Inactive Programmes without a Managing Deanery value set, of these there are 363 Programmes in HEEM where the ProgrammeName starts with Trent, Rotation or Standalone (Stand Alone);

i.e.

Row Labels	Count of LocalOfficeName
Rotation	69
Stand	3
Standalone	1
Trent	290
Grand Total	363

LocalOfficeName	ProgrammeNumber	ProgrammeName
HEEM	Trent FP 047 2009/11	047 - 2009/10 starters
HEEM	Trent FP 059	059 - 2009/10 starters
HEEM	Trent FP 034	034 - 2008/9 starters
HEEM	Rotation 47	KGH F1
HEEM	Trent FP 045	045 - 2008/9 starters
HEEM	Rotation 41	KGH F1

LocalOfficeName	ProgrammeNumber	ProgrammeName
HEEM	Stand Alone 002	Pilgrim Stand Alone 002
HEEM	Standalone SS	Notts City Small Speciality Standalone
HEEM	Stand Alone 001	Pilgrim Stand Alone 001
HEEM	Stand Alone 003	Pilgrim Stand Alone 003

There are 15,698 HELASE Inactive programmes that have no consitent ProgrammeName or Number;

LocalOfficeName	ProgrammeNumber	ProgrammeName
HELASE	KSS/RWF02/01/F2/002	KS07/02
HELASE	09/KSS/03/RWF03/02/F2/023	09/KSS/03/RWF03/02/F2/023
HELASE	12/KSS/03/RTP04/02/F2/027	10/KSS/03/RTP04/02/F2/027
HELASE	08/KSS/01/RWF02/01/F2/004	08/KSS/RWF02/01/F2/004
HELASE	11/KSS/03/RXC02/01/F1/014	10/KSS/03/RXC02/01/F1/014
HELASE	12/KSS/03/RXH01/01/F2/001	10/KSS/03/RXH01/01/F2/001
HELASE	13/KSS/03/RXC02/01/F2/013	10/KSS/03/RXC02/01/F2/013
HELASE	12/KSS/03/RYR18/01/F1/17	10/KSS/03/RYR18/01/F1/17
HELASE	KSS/RPA02/01/F2/017	KSS/RPA02/01/F2/017
HELASE	09/KSS/01/RN707/01/F1/023	09/KSS/01/RN707/01/F1/023
HELASE	15/KSS/02/RA201/01/F1/032	Created to cover 2013-14 over-subscription
HELASE	KSS/RPR01/01/F1/021	SR7/21
HELASE	08/KSS/01/RPA02/01/F1/042	08/KSS/01/RPA02/01/F1/042
HELASE	14/KSS/02/RA201/01/F1/016	10/KSS/02/RA201/01/F1/016
HELASE	15/LDN/01/RJ121/01/F1/049	10/LDN/01/RJ121/01/F1/049
HELASE	10/KSS/02/RA201/01/F1/006	10/KSS/02/RA201/01/F1/006
HELASE	09/KSS/01/RXH01/01/F1/080	09/KSS/01/RXH01/01/F1/080
HELASE	09/LDN/01/RG222/001/F1/017	09/LDN/01/RG222/001/F1/017
HELASE	13/LDN/02/RAX01/01/F1/005	10/LDN/02/RAX01/01/F1/005

There are 637 HESW Inactive programmes that have no consitent ProgrammeName or Number;

LocalOfficeName	ProgrammeNumber	ProgrammeName
HESW	PEN/RBZ12/01/F1/005	NDHT_F1_05
HESW	09/F2/111/112/113	TS/F2/03
HESW	PEN/RA901/01F1/005	TORBAY_F1_2
HESW	PEN/REF12/02/F2/013	RCHT_F2_05
HESW	WES/RW148/01/FP2/001	Hampshire P'nership Trust
HESW	SEV/RVJ20/11/F1/091/092/093/094	NB/F1/11
HESW	09/SEV/RTE01/04/F1/014	GL/F1/04
HESW	PEN/REF12/02/F2/010	RCHT_F2_03
HESW	09/F2/277/278	NB/F2/04
HESW	SEV/RTE01/05/F1/019	GL/F1/05
HESW	PEN/RBZ12/01/F2/012	NDHT_F2_02
HESW	BS4	BS4 - AUG 09

There are 52 HEWTV Inactive programmes that have no consitent ProgrammeName or Number;

LocalOfficeName	ProgrammeNumber	ProgrammeName
HEWTV	WES/RNZ01/01/FP1/001	Salisbury - FY1
HEWTV	WES/RBD01/01/FP1/001	Dorchester - FY1
HEWTV	WES/RHM01/01/FP2/002	Southampton FY2 Rotations
HEWTV	Test	Again a test
HEWTV	WES/RHM01/01/FP1/001	Southampton - FY1
HEWTV	WES/RDZ20/01/FP1/002	F1 Bournemouth 2011 (4m)
HEWTV	WES/RHU03/01/FP1/001	Portsmouth FY1 Programme
HEWTV	WES/RDZ20/001/FY2/001	Bournemouth
HEWTV	WES/RBD01/01/FP2/002	FY2/007
HEWTV	WES/FY2/RHU03/001	Portsmouth FY2
HEWTV	WES/RR201/001/YP2/001	IOW
HEWTV	NULL	Jersey Southampton GSTR1 Aug 2011
HEWTV	WES/RDZ20/01/FP1/001	Bournemouth - FY1
HEWTV	WES/RNZ01/01/FP2/001	Salisbury FY2 Rotations

There are 142 HEYH Inactive programmes that have ProgrammeName starts with Trent, Rotation, Chesterfield or Standalone (Stand Alone);

Row Labels	Count of ProgrammeNumber
CHESTERFIELD	1
Rotation	24
Stand	3
Standalone	1
Trent	113
Grand Total	142

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 TIS LocalOffice Mapping

Using the following ManagingDeanery value to TIS LocalOffice Mapping;

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

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

TIS21 Confluence Space

Programme Managing Deanery to TIS Programme Owner

ManagingDeanery to TIS LocalOffice Mapping