Actions
Jira Legacy server System JIRA columns key,summary,type,updated,assignee,status maximumIssues 20 jqlQuery labels = Posts_data_quality serverId 4c843cd5-e5a9-329d-ae88-66091fcfe3c7
For Discussion
# | Comment | Owner |
|---|---|---|
| 1 | How should the NULL MainSiteID post rows be associated with a LocalOfifce, using ManagingDeanery, Site, Trust or NationlaPostNumber prefix | Joanne Watson (Unlicensed) |
| 2 |
...
| Of the 19,303 Current Posts with a NULL ManagingDeanery, 436 have a IsTrainingPost = Unknown, 8,907 have a IsTrainingPost = Yes and the remaining 9,960 have a IsTrainingPost = No, can these Posts be ignored in TIS as they are Trust and not trainee posts. | Joanne Watson (Unlicensed) |
TIS is using the [vwPost].ManagingDeanery field to determine the Post Owner value in TIS, and will NOT be using the Hicom [vwLocalOffice] data derived via the
vwLocalOffice table using ItemType = 'Site' and UniqueID = MainSiteID
...
The data reported below has been taken from the Consolidated DR v1 on
Posts to ManagingDeanery by Status
There are 19,303 Current Post rows where the ManagingDeaneryLETB field is NOT populated
Posts to ManagingDeanery by Status
Posts to ManagingDeanery and LocalOffice by Status
Of these 19,303 Posts, the NULL ManagingDeanery are assigned to the following LocalOffices;
...
Posts to ManagingDeanery and LocalOffice by Status
Posts to ManagingDeanery and Mapped LocalOffice by Status
Using the same ManagingDeanery mapping logic as for Programmes
| 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 =IN ('Health Education England Yorkshire and the Humber', 'Yorkshire & Humber Deanery (North East)' 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' 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 |
...
| 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 |
...
= '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
Current Non Training Posts will NULL ManagingDeanery
Current Non Training Posts will NULL ManagingDeanery by ApprovedGradeName
The above case statement doesn't cater for the following 17 Managing Deaneries that are assigned to 9,256 Inactive Posts;
...
Posts to ManagingDeanery and Mapped LocalOffice by Status
Posts to ManagingDeanery, LocalOffice and Mapped LocalOffice by Status
Breaking down the Current mapped UNKOWN LocalOffice values further using the LocalOffice;
...
Posts to ManagingDeanery, LocalOffice and Mapped LocalOffice by Status
Posts with a NULL vwPost.MainSiteID value
How to identify the LocalOffice for Posts with a NULL MainSiteID field;
...