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

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

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;

Code Block

	sql
	sql


case when p.ManagingDeanerymanagingdeaneryletb = 'Health Education England North West' THEN 'HENW'
			when p.ManagingDeanerymanagingdeaneryletb = 'Health Education England West Midlands' THEN 'HEWM'
			when p.ManagingDeanerymanagingdeaneryletb = 'Health Education England Yorkshire and the Humber' THEN 'HEYH'
			when p.ManagingDeanerymanagingdeaneryletb = 'Health Education England North East' THEN 'HENE'
			when p.ManagingDeanerymanagingdeaneryletb = 'Health Education England East of England' THEN 'HEEOE'
			when p.ManagingDeanerymanagingdeaneryletb IN ('Health Education England South West',

									   'Peninsula Local Office - HESW',

									   'Severn Local Office - HESW') THEN 'HESW'
			when p.ManagingDeanerymanagingdeaneryletb IN ('Health Education England East Midlands',

									   'Yorkshire & Humber Deanery (North East)') THEN 'HEEM'
			when p.ManagingDeanerymanagingdeaneryletb IN ('Health Education England Kent, Surrey and Sussex',

									   'London LETBs',

								       'Health Education England North West London',

								       'Health Education England North North CentralCentral and East London',

								       'Health Education England South London') THEN 'HELASE'
			when p.ManagingDeanerymanagingdeaneryletb IN ('Health Education England Wessex', 'Health 
	                               'Health Education England Thames Valley') THEN 'HEWTV'
			else 'UNKNOWN'
		endend 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]                         '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

...

Version	Old Version 10	New Version Current
Changes made by	Russell Steel	Russell Steel
Saved on	05 Oct 2017	11 Oct 2017

Versions Compared

Key

ManagingDeanery to TIS LocalOffice Mapping

Page Comparison

Versions Compared

Key

ManagingDeanery to TIS LocalOffice Mapping