...
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;
...
This is the distribution of the Hicom LocalOffice table data to Programmes;
...
...
There are 153 Programmes that have been assigned to multiple LocalOffices;
...
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 on (lo.ItemType = 'Programme' 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;
...
If using the smart search in TIS for Programmes, by Programme Number or Name, searching the following values will cause duplicates to be returned;
...
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')
Details on the 2 duplicated Current Programmes;
...
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;
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;
...
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;
...
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