SQL

A. FormR local offices standardised:

UPDATE form SET localOfficeNameStd = 'East Midlands' WHERE localOfficeName like '%East Midlands%';
UPDATE form SET localOfficeNameStd = 'East of England' WHERE localOfficeName like '%East of England%';
UPDATE form SET localOfficeNameStd = 'Kent, Surrey and Sussex' WHERE localOfficeName like '%Kent%Surrey and Sussex%';
UPDATE form SET localOfficeNameStd = 'North Central and East London' WHERE localOfficeName like '%North Central and East London%';
UPDATE form SET localOfficeNameStd = 'Wessex' WHERE localOfficeName like '%Wessex%';
UPDATE form SET localOfficeNameStd = 'Yorkshire and the Humber' WHERE localOfficeName like '%Yorkshire and the Humber%';
UPDATE form SET localOfficeNameStd = 'North East' WHERE localOfficeName like '%North East%';
UPDATE form SET localOfficeNameStd = 'South London' WHERE localOfficeName like '%South London%';
UPDATE form SET localOfficeNameStd = 'North West' WHERE localOfficeName like '%North West%';
UPDATE form SET localOfficeNameStd = 'West Midlands' WHERE localOfficeName like '%West Midlands%';
UPDATE form SET localOfficeNameStd = 'North West London' WHERE localOfficeName like '%North West London%';
UPDATE form SET localOfficeNameStd = 'South West' WHERE localOfficeName like '%South West%';
UPDATE form SET localOfficeNameStd = 'Thames Valley' WHERE localOfficeName like '%Thames Valley%';
UPDATE form SET localOfficeNameStd = 'London LETBs' WHERE localOfficeName like '%London LETBs%';

B.1. TIS programme names standardised:

case
when pr.ProgrammeName like '%child%Adolescent%' then 'Child and Adolescent Psychiatry'
when pr.ProgrammeName like '%Core%Psychiatry%' then 'Core Psychiatry Training'
when pr.ProgrammeName like '%forensic%Psychiatry%' then 'Forensic Psychiatry'
when pr.ProgrammeName like '%General%Psychiatry%' then 'General Psychiatry'
when pr.ProgrammeName like '%Medical%Psychotherapy%' then 'Medical Psychotherapy'
when pr.ProgrammeName like '%Old%Age%Psychiatry%' then 'Old Age Psychiatry'
when pr.ProgrammeName like '%Psychiatry%Learning%' then 'Psychiatry of Learning Disability'
when pr.ProgrammeName like '%general%Practice%' or pr.ProgrammeName like '%GP%' then 'General Practice'
when pr.ProgrammeName like '%Neurosurgery%' then 'Neurosurgery'
when pr.ProgrammeName like '%plastic%Surgery%' then 'Plastic Surgery'
when pr.ProgrammeName like '%Acute Care Common Stem%Anaesthetics%' or pr.ProgrammeName like '%ACCS%Anaesthetics%' then 'ACCS - Anaesthetics'
when pr.ProgrammeName like '%Acute Care Common Stem%Intensive%' or pr.ProgrammeName like '%ACCS%Intensive%' then 'ACCS - Intensive Care Medicine'
when pr.ProgrammeName like '%Acute Care Common Stem%Emergency%' or pr.ProgrammeName like '%ACCS%Emergency%' then 'ACCS - Emergency Medicine'
when pr.ProgrammeName like '%Acute Care Common Stem%Acute%' or pr.ProgrammeName like '%ACCS%Acute%' then 'ACCS - Acute Medicine'
when pr.ProgrammeName like '%Acute Care Common Stem%' then 'ACCS'
when pr.ProgrammeName like '%Dental%Foundation%' then 'Dental Foundation Training'
when pr.ProgrammeName like '%Core%Anaesthetics%' then 'Core Anaesthetics Training'
when pr.ProgrammeName like '%Core%Surgical%' then 'Core Surgical Training'
when pr.ProgrammeName like '%Core%Medical%' then 'Core Medical Training'
when pr.ProgrammeName like '%Anaesthetics%' then 'Anaesthetics'
when pr.ProgrammeName like '%Oral%Maxillo%' then 'Oral and Maxillo-Facial Surgery'
when pr.ProgrammeName like '%Oral%Surgery%' then 'Oral Surgery'
when pr.ProgrammeName like '%Foundation%' then 'Foundation'
when pr.ProgrammeName like '%Clinical%Radiology%' then 'Clinical Radiology'
when pr.ProgrammeName like '%Palliative%Medicine%' then 'Palliative Medicine'
when pr.ProgrammeName like '%Emergency%Medicine%' then 'Emergency Medicine'
when pr.ProgrammeName like '%Ophthalmology%' then 'Ophthalmology'
when pr.ProgrammeName like '%Otolaryngology%' then 'Otolaryngology'
when pr.ProgrammeName like '%Paediatric%Cardiology%' then 'Paediatric Cardiology'
when pr.ProgrammeName like '%Cardiology%' then 'Cardiology'
when pr.ProgrammeName like '%Paediatric%Surgery%' then 'Paediatric Surgery'
when pr.ProgrammeName like '%Haematology%' then 'Haematology'
when pr.ProgrammeName like '%Rheumatology%' then 'Rheumatology'
when pr.ProgrammeName like '%Combined%Infection%' then 'Combined Infection Training'
when pr.ProgrammeName like '%Clinical%Genetics%' then 'Clinical Genetics'
when pr.ProgrammeName like '%Obstetrics%Gynaecology%' then 'Obstetrics and Gynaecology'
when pr.ProgrammeName like '%Endocrinology%diabetes%' then 'Endocrinology and Diabetes Mellitus'
when pr.ProgrammeName like '%Dental%Public%Health%' then 'Dental Public Health'
when pr.ProgrammeName like '%Public%Health%%Dental%' then 'Dental Public Health'
when pr.ProgrammeName like '%Public%Health%' then 'Public Health Medicine'
when pr.ProgrammeName like '%Intensive%Care%Medicine%' then 'Intensive Care Medicine'
when pr.ProgrammeName like '%Genito%Urinary%' then 'Genito-Urinary Medicine'
when pr.ProgrammeName like '%Medical%Oncology%' then 'Medical Oncology'
when pr.ProgrammeName like '%Neurology%' then 'Neurology'
when pr.ProgrammeName like '%Urology%' then 'Urology'
when pr.ProgrammeName like '%Forensic%Histopathology%' then 'Forensic Histopathology'
when pr.ProgrammeName like '%Paediatrics%' then 'Paediatrics'
when pr.ProgrammeName like '%Orthodontics%' then 'Orthodontics'
when pr.ProgrammeName like '%Histopathology%' then 'Histopathology'
when pr.ProgrammeName like '%Cardio%thoracic%' then 'Cardiothoracic Surgery'
when pr.ProgrammeName like '%Medical%Microbiology%Virology%' then 'Medical Microbiology and Virology'
when pr.ProgrammeName like '%Medical%Microbiology%' then 'Medical Microbiology'
when pr.ProgrammeName like '%Immunology%' then 'Immunology'
when pr.ProgrammeName like '%Respiratory%Medicine%' then 'Respiratory Medicine'
when pr.ProgrammeName like '%Infectious%Diseases%' then 'Infectious Diseases'
when pr.ProgrammeName like '%Medical%Virology%' then 'Medical Virology'
when pr.ProgrammeName like '%Vascular%Surgery%' then 'Vascular Surgery'
when pr.ProgrammeName like '%Dental%Public%Health' then 'Dental Public Health'
when pr.ProgrammeName like '%Rehabilitation%Medicine%' then 'Rehabilitation Medicine'
when pr.ProgrammeName like '%Renal%Medicine%' then 'Renal Medicine'
when pr.ProgrammeName like '%Broad%Based%Training%' Then 'Broad Based Training'
when pr.ProgrammeName like '%Chemical%Pathology%' then 'Chemical Pathology'
when pr.ProgrammeName like '%Special%Care%Dentistry%' then 'Special Care Dentistry'
when pr.ProgrammeName like '%Gastro%enterology%' then 'Gastroenterology'
when pr.ProgrammeName like '%Nuclear%Medicine%' then 'Nuclear Medicine'
when pr.ProgrammeName like '%Oral%Maxillo%Pathology%' then 'Oral and Maxillo-Facial Pathology'
when pr.ProgrammeName like '%Audio%Vestibular%Medicine%' then 'Audio Vestibular Medicine'
when pr.ProgrammeName like '%Trauma%Orthopaedic%' then 'Trauma and Orthopaedic Surgery'
when pr.ProgrammeName like '%Stroke%Medicine%' then 'Stroke Medicine'
when pr.ProgrammeName like '%Internal%Medicine%Stage%' then 'Internal Medicine Training Stage 1'
when pr.ProgrammeName like '%Restorative%Dentistry%' then 'Restorative Dentistry'
when pr.ProgrammeName like '%Acute%Internal%Medicine%' then 'Acute Internal Medicine'
when pr.ProgrammeName like '%Community%Sexual%Reproductive%' then 'Community Sexual and Reproductive Health'
when pr.ProgrammeName like '%Paediatric%Dentistry%' or pr.programmename like 'Paeds%Dentistry%' then 'Paediatric Dentistry'
when pr.ProgrammeName like '%Dermatology%' then 'Dermatology'
when pr.ProgrammeName like '%Dental%Core%' then 'Dental Core Training'
when pr.ProgrammeName like '%Allergy%' then 'Allergy'
when pr.ProgrammeName like '%Geriatric%' then 'Geriatric Medicine'
when pr.ProgrammeName like '%General%Internal%' then 'General (Internal) Medicine'
when pr.ProgrammeName like '%Clinical%Pharmacology%' then 'Clinical Pharmacology and Therapeutics'
when pr.ProgrammeName like '%Diagnostic%europathology%' then 'Diagnostic Neuropathology'
when pr.ProgrammeName like '%Sport%Exercise%' then 'Sport and Exercise Medicine'
when pr.ProgrammeName like '%Clinical%Oncology%' then 'Clinical Oncology'
when pr.ProgrammeName like '%Paediatric%Perinatal%Pathology%' then 'Paediatric and Perinatal Pathology'
when pr.ProgrammeName like '%General%Surgery%' then 'General Surgery'
when pr.ProgrammeName like '%Dental%Maxillo%facial%Radiology%' then 'Dental and Maxillofacial Radiology'
when pr.ProgrammeName like '%Oral%Medicine%' then 'Oral Medicine'
when pr.ProgrammeName like '%Endodontics%' then 'Endodontics'
when pr.ProgrammeName like '%Periodontics%' then 'Periodontics'
when pr.ProgrammeName like '%Occupational%Medicine%' then 'Occupational Medicine'
when pr.ProgrammeName like '%Clinical%Neurophysiology%' then 'Clinical Neurophysiology'
when pr.ProgrammeName like '%Dental%Integrated%' then 'Dental - Integrated DFT and DCT Training'
when pr.ProgrammeName like '%Dental%Longitudinal%' then 'Dental Longitudinal Training'
when pr.ProgrammeName like ‘%Longitudinal%Dental%' then 'Dental Longitudinal Training'
when pr.ProgrammeName like '%Prosthodontics%' then 'Prosthodontics'
when pr.ProgrammeName like '%Aviation%Space%Medicine%' then 'Aviation and Space Medicine'
when pr.ProgrammeName like '%Dental%Specialties%' then 'Dental Medical Specialties'
when pr.ProgrammeName like '%WAST%' then 'WAST'
when pr.ProgrammeName like '%Post-CCT%%Academics%' then 'Post-CCT Academics'
End

Note that TCS Programme names are standardised, but in the scripts below they are matched against unstandardised form entries (programme specialty, CCT1, CCT2, dual specialty).

B.2. A 'parent’ programme:

SET parent = case
when sp.stdSpecialtyProgramme in ('Foundation') then 'Foundation'
when sp.stdSpecialtyProgramme in
(
'ACCS','ACCS - Acute Medicine',
'ACCS - Anaesthetics',
'ACCS - Emergency Medicine',
'ACCS - Intensive Care Medicine',
'Anaesthetics',
'Intensive Care Medicine',
'Emergency Medicine',
'Core Anaesthetics Training')
then 'Ana/ICM/EM'
when sp.stdSpecialtyProgramme in (
'Acute Internal Medicine',
'General (Internal) Medicine',
'Gastroenterology',
'Endoncrinology and Diabetes Mellitus',
'Geriatric Medicine',
'Respiratory Medicine',
'Cardiology',
'Neurology',
'Stroke Medicine',
'Allergy',
'Audio Vestibular Medicine',
'Avaiation and Space Medicine',
'Clinical Genetics',
'Clinical Neurophysiology',
'Clinical Pharmacology and Therapeutics',
'Core Medical Training',
'Dermatology',
'Genito-Urinary Medicine',
'Haematology',
'Immunology',
'Histopathology%',
'Immunology',
'Infectious Diseases',
'Medical Oncology',
'Nuclear Medicine',
'Occupational Medicine',
'Palliative Medicine',
'Rehabilitation Medicine',
'Renal Medicine',
'Rheumatology',
'Sport and Exercise Medicine',
'Paediatric Cardiology',
'Internal Medicine Training Stage 1'
)
then 'Medicine'
when sp.stdSpecialtyProgramme in ('Community Sexual and Reproductive Health','Obstetrics and Gynaecology') then 'O&G'
when sp.stdSpecialtyProgramme in ('Ophthalmology') then 'Ophthalmology'
when sp.stdSpecialtyProgramme in ('Paediatrics') then 'Paediatrics'
when sp.stdSpecialtyProgramme in
(
'Chemical Pathology',
'Combined Infection Training',
'Diagnostic Neuropathology',
'Forensic Histopathology',
'Histopathology',
'Medical Microbiology',
'Medical Microbiology and Virology',
'Medical Virology',
'Paediatric and Perinatal Pathology'
)
then 'Pathology'
when sp.stdSpecialtyProgramme in
(
'Child and Adolescent Psychiatry',
'Core Psychiatry Training',
'Forensic Psychiatry',
'General Psychiatry',
'Medical Psychotherapy',
'Old Age Psychiatry',
'Psychiatry of Learning Disability'
)
then 'Psychiatry'
when sp.stdSpecialtyProgramme in ('Clinical Oncology','Clinical Radiology') then 'Radiology - Cancer'
when sp.stdSpecialtyProgramme in
(
'Cardiothoracic Surgery',
'Core Surgical Training',
'General Surgery',
'Neurosurgery',
'Oral and Maxillo-Facial Surgery',
'Otolaryngology',
'Paediatric Surgery',
'Plastic Surgery',
'Trauma and Orthopaedic Surgery',
'Urology',
'Vascular Surgery'
)
then 'Surgery'
when sp.stdSpecialtyProgramme in ('Public Health Medicine') then 'Public Health'
when sp.stdSpecialtyProgramme in ('General Practice') then 'GP'
when sp.stdSpecialtyProgramme in
(
'Dental - Integrated DFT and DCT Training',
'Dental and Maxillofacial Radiology',
'Dental Core Training',
'Dental Foundation Training',
'Dental Longitudinal Training',
'Dental Medical Specialties',
'Endodontics',
'Oral Medicine',
'Oral Surgery',
'Orthodontics',
'Periodontics',
'Paediatric Dentistry',
'Prosthodontics',
'Special Care Dentistry',
'Restorative Dentistry',
'Dental Public Health'
)
then 'Dentistry'
End

C. Single PM linking rule:

CREATE DEFINER=`root`@`%` PROCEDURE `1_set_single_pm_link`()
BEGIN
	UPDATE stats_formra f 
    JOIN tcs.ProgrammeMembership pm ON f.traineeTisId = pm.personId 
    JOIN (SELECT personId, count(*) FROM tcs.ProgrammeMembership GROUP BY personId HAVING count(*) = 1) singlePms ON f.traineeTisId = singlePms.personId
    SET linkedPmUuid = pm.uuid, linkedBy = "1. Single PM";
    
    UPDATE stats_formrb f
    JOIN tcs.ProgrammeMembership pm ON f.traineeTisId = pm.personId
    JOIN (SELECT personId, COUNT(*) FROM tcs.ProgrammeMembership GROUP BY personId HAVING COUNT(*) = 1) singlePms ON f.traineeTisId = singlePms.personId 
    SET linkedPmUuid = pm.uuid, linkedBy = "1. Single PM";
END

D. Unique programme membership within submission window:

CREATE DEFINER=`root`@`%` PROCEDURE `3x_kav_indate_a`()
BEGIN
    UPDATE stats_formra f 
    JOIN rr.formra wtea ON f.id = wtea.id
    JOIN (SELECT pm.personId
			,pm.programmeID
			,p.stdSpecialtyProgramme
			,p.programmeNumber
			,pp.owner AS managingDeanery
			,pm.programmeStartDate
			,cm.curriculumCompletionDate
			,c.name AS curriculumName
			,s.name AS specialtyName
			,pm.uuid AS programmeMembershipUuid
    
			FROM tcs.CurriculumMembership cm
            JOIN tcs.Curriculum c ON cm.curriculumId = c.id
            JOIN tcs.Specialty s ON c.specialtyId = s.id
            JOIN tcs.ProgrammeMembership pm ON cm.programmeMembershipUuid = pm.uuid
			LEFT JOIN rr.stdProgramme p ON pm.ProgrammeID = p.programmeTisId
            LEFT JOIN tcs.Programme pp ON pm.ProgrammeID = pp.id
  
			WHERE s.name NOT IN ('Academic', 'Foundation')
		) c
			ON c.personId = wtea.traineeTisId 
			AND c.managingDeanery = wtea.localOfficeNameStd
			AND (
                 c.stdSpecialtyProgramme = wtea.programmeSpecialty 
				OR c.stdSpecialtyProgramme = wtea.cctSpecialty1
				OR c.stdSpecialtyProgramme = wtea.cctSpecialty2
				OR c.specialtyName = wtea.programmeSpecialty
				OR c.specialtyName = wtea.cctSpecialty1
				OR c.specialtyName = wtea.cctSpecialty2
				OR c.curriculumName = wtea.programmeSpecialty
				OR c.CurriculumName = wtea.cctSpecialty1
				OR c.CurriculumName = wtea.cctSpecialty2
				)
SET 
    f.linkedPmUuid = c.programmeMembershipUuid, linkedBy = "3x. Kav indate" 
    WHERE linkedPmUuid IS NULL AND
    pm_current_in_window(c.programmeMembershipUuid, wtea.submissionDateDate, 12, 4) = 1 AND -- ended within 12 months before submission date, starts within 4 months after submission date
    pms_current_in_window(f.traineeTisId, wtea.submissionDateDate, 12, 4) = 1; -- only pm that falls in the window
END

CREATE DEFINER=`root`@`%` PROCEDURE `3x_kav_indate_b`()
BEGIN
UPDATE stats_formrb f 
    JOIN rr.formrb wtea ON f.id = wtea.id
    JOIN (SELECT pm.personId
		  ,pm.programmeID
		  ,p.stdSpecialtyProgramme
	      ,p.programmeNumber
		  ,pp.owner as managingDeanery
		  ,pm.programmeStartDate
		  ,cm.curriculumCompletionDate
		  ,c.name as curriculumName
		  ,s.name as specialtyName
		  ,pm.uuid as programmeMembershipUuid
    
		  FROM tcs.CurriculumMembership cm
          JOIN tcs.Curriculum c ON cm.curriculumId = c.id
          JOIN tcs.Specialty s ON c.specialtyId = s.id
          JOIN tcs.ProgrammeMembership pm ON cm.programmeMembershipUuid = pm.uuid
		  LEFT JOIN rr.stdProgramme p ON pm.ProgrammeID = p.programmeTisId
          LEFT JOIN tcs.Programme pp ON pm.ProgrammeID = pp.id
  
		  WHERE s.name NOT IN ('Academic', 'Foundation') 
		) c
			ON c.personId = wtea.traineeTisId 
			AND c.managingDeanery = wtea.localOfficeNameStd
			AND (	
              c.stdSpecialtyProgramme = wtea.programmeSpecialty 
			  OR c.stdSpecialtyProgramme = wtea.dualSpecialty
			  OR c.specialtyName = wtea.programmeSpecialty
              OR c.specialtyName = wtea.dualSpecialty
			  OR c.curriculumName = wtea.programmeSpecialty
              OR c.curriculumName = wtea.dualSpecialty
			)
SET 
    f.linkedPmUuid = c.programmeMembershipUuid, linkedBy = "3x. Kav indate" 
    WHERE linkedPmUuid IS NULL AND
    pm_current_in_window(c.programmeMembershipUuid, wtea.submissionDateDate, 12, 4) = 1 AND -- ended within 12 months before submission date, starts within 4 months after submission date
    pms_current_in_window(f.traineeTisId, wtea.submissionDateDate, 12, 4) = 1; -- only pm that falls in the window
END

Functions used in above:

Is the given PM current within the defined window around the submission date:

How many of a trainee’s PMs are current within the defined window around the submission date:

E. Unique programme membership matching local office and programme details within submission window:

Iterate with wtea.programmeSpecialty in function calls replaced by wtea.cctSpecialty1 and then again by wtea.cctSpecialty2 (for performance reasons and to prioritise programme specialty over CCT1 and CCT2).

Iterate with wtea.programmeSpecialty in function calls replaced by wtea.dualSpecialty (for performance reasons, and to prioritise programme specialty over dual specialty).

Functions used in above:

Is the given PM current within the defined window around the submission date, and does it have the given local office and specialty:

How many of a trainee’s PMs for the given local office and specialty are current within the defined window around the submission date:

TIS21 Confluence Space

SQL