Page Comparison

...

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;

Image Added

...

Programme Consolidation Metabase Dashboard

...

Programme by LocalOfficeName and Status;

...

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;

...

There are 153 Programmes that have been assigned to multiple LocalOffices;

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;

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

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;

duplicated ProgrammeNumbers

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;

Current duplicated ProgrammeNumber