Programmes - Consolidation

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.

Alistair Pringle (Unlicensed)/Former user (Deleted)/Joanne Watson (Unlicensed)

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.

Alistair Pringle (Unlicensed)/Former user (Deleted)/Joanne Watson (Unlicensed)

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;

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;

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

select ProgrammeNumber, ProgrammeName, status, count(*)
from [HEELIVE].[vwProgramme] as p
group by ProgrammeNumber, ProgrammeName, status
having count(*) > 1
order by 3,4 desc;

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