Versions Compared

Version Old Version 12 New Version 13
Changes made by

Reuben Roberts

Reuben Roberts

Saved on

Key

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

...

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