Page Comparison

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 

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;

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
						from by 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;

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;

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 

...

#

...

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.

...

...

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.

...

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;

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