Versions Compared

Key

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

...

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

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;

Statusmanagingdeaneryletbmapped_lorc
CurrentNULLUNKNOWN19303
CurrentHealth Education England North?Central and East LondonUNKNOWN5179
CurrentDefence Postgraduate Medical DeaneryUNKNOWN47
CurrentSouth West Peninsula DeaneryUNKNOWN33
CurrentWales DeaneryUNKNOWN14
CurrentYorkshire & Humber Deanery (West)UNKNOWN9
CurrentEast Midlands HWD (North)UNKNOWN5
CurrentNon NHS AuthorityUNKNOWN2
CurrentYorkshire & Humber Deanery (South)UNKNOWN2
CurrentLondon DeaneryUNKNOWN1

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


StatusIsTrainingPostrc
CurrentUnknown436
CurrentYes8907
CurrentNo9960

Current Posts with a NULL Managing Deanery by IsTrainingPost

The above case statement doesn't cater for the following 17 Managing Deaneries that are assigned to 9,256 Inactive Posts;

...