Versions Compared

Version Old Version 12 New Version 13
Changes made by

Russell Steel

Russell Steel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

...

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

...