...
Code Block | ||||
---|---|---|---|---|
| ||||
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;
Status | managingdeaneryletb | mapped_lo | rc |
---|---|---|---|
Current | NULL | UNKNOWN | 19303 |
Current | Health Education England North?Central and East London | UNKNOWN | 5179 |
Current | Defence Postgraduate Medical Deanery | UNKNOWN | 47 |
Current | South West Peninsula Deanery | UNKNOWN | 33 |
Current | Wales Deanery | UNKNOWN | 14 |
Current | Yorkshire & Humber Deanery (West) | UNKNOWN | 9 |
Current | East Midlands HWD (North) | UNKNOWN | 5 |
Current | Non NHS Authority | UNKNOWN | 2 |
Current | Yorkshire & Humber Deanery (South) | UNKNOWN | 2 |
Current | London Deanery | UNKNOWN | 1 |
Of the above 19,303 Current Posts with a NULL Managing Deanery, they have the following IsTrainingPost values;
Code Block | ||||
---|---|---|---|---|
| ||||
select Status, p.IsTrainingPost, count(*) as rc
from [HEELIVE].[vwPost] as p
where status = 'Current'
and managingdeaneryletb IS NULL
group by Status, p.IsTrainingPost |
Status | IsTrainingPost | rc |
---|---|---|
Current | Unknown | 436 |
Current | Yes | 8907 |
Current | No | 9960 |
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;
...