Versions Compared

Version Old Version 12 New Version Current
Changes made by

Reuben Roberts

Reuben Roberts

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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:

...

Code Block
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:

Code Block
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

...

Code Block
CREATE DEFINER=`root`@`%` FUNCTION `pms_current_in_window`(traineeId VARCHAR(50), dt DATE, mnthsmarginbefore INTEGER,  mnthsmarginafter INTEGER) RETURNS int(11)
BEGIN
  DECLARE PMS INTEGER DEFAULT 0;

SELECT 
    COUNT(*)
INTO PMS FROM
    tcs.ProgrammeMembership
WHERE
    personId = traineeId
        AND DATE_ADD(programmeEndDate,
        INTERVAL mnthsmarginbefore MONTH) >= dt
        AND DATE_ADD(programmeStartDate,
        INTERVAL - 1 * mnthsmarginafter MONTH) <= dt;
  RETURN PMS;
END

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

Code Block
CREATE DEFINER=`root`@`%` PROCEDURE `4x_kav_indate_lo_p_a`()
BEGIN
    UPDATE stats_formra f 
    JOIN rr.formra wtea ON f.id = wtea.id
    JOIN (SELECT pm.personIdstr as 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 = "4x. Kav indate LO+P" 
    WHERE linkedPmUuid IS NULL AND
    pm_current_in_window_lo_p(c.programmeMembershipUuid, wtea.submissionDateDate, 12, 4, c.managingDeanery, wtea.programmeSpecialty) = 1 AND -- ended within 12 months before submission date, starts within 4 months after submission date
    pms_current_in_window_lo_p(f.traineeTisId, wtea.submissionDateDate, 12, 4, c.managingDeanery, wtea.programmeSpecialty) = 1; -- only pm that falls in the window
END

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).

Code Block
CREATE DEFINER=`root`@`%` PROCEDURE `4x_kav_indate_lo_p_b`()
BEGIN
UPDATE stats_formrb f 
    JOIN rr.formrb wtea ON f.id = wtea.id
    JOIN (SELECT pm.personIdstr as 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 = "4x. Kav indate LO+P" 
    WHERE linkedPmUuid IS NULL AND
    pm_current_in_window_lo_p(c.programmeMembershipUuid, wtea.submissionDateDate, 12, 4, c.managingDeanery, wtea.programmeSpecialty) = 1 AND -- ended within 12 months before submission date, starts within 4 months after submission date
    pms_current_in_window_lo_p(f.traineeTisId, wtea.submissionDateDate, 12, 4, c.managingDeanery, wtea.programmeSpecialty) = 1; -- only pm that falls in the window
END

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:

Code Block
CREATE DEFINER=`root`@`%` FUNCTION `pm_current_in_window_lo_p`(theuuid VARCHAR(50), dt DATE, mnthsmarginbefore INTEGER, mnthsmarginafter INTEGER, lo VARCHAR(250), programmeSpecialty VARCHAR(250)) RETURNS tinyint(1)
BEGIN
	DECLARE CNT INTEGER DEFAULT 0;
SELECT 
    COUNT(*)
INTO CNT 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
    pm.uuid = theuuid
    AND pp.owner = lo
    AND (
                 p.stdSpecialtyProgramme = programmeSpecialty 
				OR s.name = programmeSpecialty
				OR c.name = programmeSpecialty				
				)
    AND 
        DATE_ADD(programmeEndDate, INTERVAL mnthsmarginbefore MONTH) >= dt
        AND DATE_ADD(programmeStartDate, INTERVAL - 1 * mnthsmarginafter MONTH) <= dt;
        
  RETURN CNT > 0;
END

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

Code Block
CREATE DEFINER=`root`@`%` FUNCTION `pms_current_in_window_lo_p`(traineeId VARCHAR(50), dt DATE, mnthsmarginbefore INTEGER,  mnthsmarginafter INTEGER, lo VARCHAR(250), programmeSpecialty VARCHAR(250)) RETURNS int(11)
BEGIN
  DECLARE PMS INTEGER DEFAULT 0;

SELECT 
    COUNT(*)
INTO PMS 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
    personId = traineeId
    AND pp.owner = lo
    AND (
				p.stdSpecialtyProgramme = programmeSpecialty 
				OR s.name = programmeSpecialty
				OR c.name = programmeSpecialty
				)
        AND DATE_ADD(programmeEndDate,
        INTERVAL mnthsmarginbefore MONTH) >= dt
        AND DATE_ADD(programmeStartDate,
        INTERVAL - 1 * mnthsmarginafter MONTH) <= dt;
  RETURN PMS;
END