Programme by LocalOfficeName and Status;
...
select LocalOfficeName, Status, count(*)
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID)
group by LocalOfficeName, Status
order by 2,1;
...
There are 153 Programmes that have been assigned to multiple LocalOffices;
...
For Discussion
# |
---|
# | Comment | Owner |
---|---|---|
1 | Should the Programme Name and Number be unique ? Programme Name is not unique. I'm not sure on whether an agreement on naming convention has been agreed in consolidation but there will be many duplicate programmes. | |
2 | Should the Programme Number be unique ? The programme number is unique. This number is assigned by the GMC to each local Programme when it gains first approval. |
Hicom and HEE have completed the consolidation of 9 separate Intrepid DBs, into a single DB. As part of the consolidation, Hicom have created the table vwLocalOffice to trace the consolidated data back to the DB origin;
...
Programme Consolidation Metabase Dashboard
...
Programme by LocalOfficeName and Status;
Code Block | ||||
---|---|---|---|---|
| ||||
select LocalOfficeName, Status, count(*) from [HEELIVE].[vwProgramme] as p join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID) wheregroup p.ProgrammeID in (select UniqueID fromby LocalOfficeName, Status order by 2,1; |
This is the distribution of the Hicom LocalOffice table data to Programmes;
Programme by LocalOfficeName and Status
There are 153 Programmes that have been assigned to multiple LocalOffices;
Code Block | ||||
---|---|---|---|---|
| ||||
select LocalOfficeName, Status, count(*) from [HEELIVE].[vwProgramme] as p join [HEELIVE].[vwLocalOffice] as lo where on (lo.ItemType = 'Programme' group by UniqueID and lo.UniqueID = p.ProgrammeID) where p.ProgrammeID in (select UniqueID from [HEELIVE].[vwLocalOffice] as lo where lo.ItemType = 'Programme' group by UniqueID having count(*) > 1) group by LocalOfficeName, Status order by 2,1; |
...
The following LocalOffices have shared Programmes within them;
Programmes that have been assigned to multiple LocalOffices
There are 70 Programmes with duplicated ProgrammNumber and ProgrammeName, though only 2 are still active Programmes;
Code Block | ||||
---|---|---|---|---|
| ||||
select ProgrammeNumber, ProgrammeName, status, count(*) from [HEELIVE].[vwProgramme] as p group by ProgrammeNumber, ProgrammeName, status having count(*) > 1 order by 3,4 desc; |
...
Of the 2 active duplicate Programmes, this is the details;
...
select lo.LocalOfficeName, p.*
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID)
where ProgrammeNumber in ('NOR2137','YAH710')
...
There are 818 duplicated ProgrammeNumbers, of which 49 are active
...
select ProgrammeNumber, status, count(*)
from [HEELIVE].[vwProgramme] as p
group by ProgrammeNumber, status
having count(*) > 1
order by 2,3 desc;
...
The 49 Current ProgrammeNumber = 219 Programmes;
...
select lo.LocalOfficeName, p.*
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID)
where ProgrammeNumber in (select ProgrammeNumber
from [HEELIVE].[vwProgramme] as p
where status = 'Current'
group by ProgrammeNumber, status
having count(*) > 1)
order by ProgrammeNumber
...
For Discussion
...
#
...
Comment
...
If using the smart search in TIS for Programmes, by Programme Number or Name, searching the following values will cause duplicates to be returned;
Programmes with duplicated ProgrammNumber and ProgrammeName
Of the 2 active duplicate Programmes, this is the details;
Code Block | ||||
---|---|---|---|---|
| ||||
select lo.LocalOfficeName, p.*
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID)
where exists (select ProgrammeNumber, ProgrammeName
from [HEELIVE].[vwProgramme] as pc
where pc.status = 'Current'
and pc.ProgrammeNumber = p.ProgrammeNumber
and pc.ProgrammeName = p.ProgrammeName
group by ProgrammeNumber, ProgrammeName
having count(*) > 1) |
Details on the 2 duplicated Current Programmes;
active duplicate Programmes Names and Numbers
There are 818 duplicated ProgrammeNumbers, of which 49 are active
Code Block | ||||
---|---|---|---|---|
| ||||
select ProgrammeNumber, status, count(*)
from [HEELIVE].[vwProgramme] as p
group by ProgrammeNumber, status
having count(*) > 1
order by 2,3 desc; |
If using the smart search in TIS for Programmes, by Programme Number, searching the following values will cause duplicates to be returned;
The 49 Current ProgrammeNumber = 219 Programmes;
Code Block | ||||
---|---|---|---|---|
| ||||
select lo.LocalOfficeName, p.*
from [HEELIVE].[vwProgramme] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Programme' and lo.UniqueID = p.ProgrammeID)
where ProgrammeNumber in (select ProgrammeNumber
from [HEELIVE].[vwProgramme] as p
where status = 'Current'
group by ProgrammeNumber, status
having count(*) > 1)
order by ProgrammeNumber |
Details on the 49 duplicated Current Programmes Numbers;
Current duplicated ProgrammeNumber