...
| 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 LocalOffice Mapping
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 |
...