...
| Code Block |
|---|
CREATE DEFINER=`root`@`%` PROCEDURE `2_kav_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') AND
((now() BETWEEN cm.CurriculumStartDate AND cm.CurriculumEndDate) or cm.CurriculumStartDate> now())
) 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 = "2. Kav rules"
WHERE linkedPmUuid IS NULL AND
pm_current_buffered_months(c.programmeMembershipUuid, wtea.submissionDateDate, 6) AND -- started 6+ months before, finishes 6+ months after submission date
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 6) = 1 AND -- only pm current 6+ months before, 6+ months after
pms_current_buffered_months(f.traineeTisId, wtea.submissionDateDate, 0) = 1; -- only pm current when submitted
END |
Functions used in above:
Is the given PM current at a given date plus/minus a given buffer of months:
| Code Block |
|---|
CREATE DEFINER=`root`@`%` FUNCTION `pm_current_buffered_months`(theuuid VARCHAR(50), dt DATE, mnthsmargin INTEGER) RETURNS tinyint(1)
BEGIN
DECLARE CNT INTEGER DEFAULT 0;
SELECT
COUNT(*)
INTO CNT FROM
tcs.ProgrammeMembership
WHERE
uuid = theuuid
AND DATE_ADD(programmeStartDate,
INTERVAL mnthsmargin MONTH) <= dt
AND DATE_ADD(programmeEndDate,
INTERVAL - 1 * mnthsmargin MONTH) >= dt;
RETURN CNT > 0;
END |
How many PMs are current at a given date plus/minus a given buffer of months:
| Code Block |
|---|
CREATE DEFINER=`root`@`%` FUNCTION `pms_current_buffered_months`(traineeId VARCHAR(50), dt DATE, mnthsmargin INTEGER) RETURNS int(11)
BEGIN
DECLARE PMS INTEGER DEFAULT 0;
SELECT
COUNT(*)
INTO PMS FROM
tcs.ProgrammeMembership
WHERE
personId = traineeId
AND DATE_ADD(programmeStartDate,
INTERVAL mnthsmargin MONTH) <= dt
AND DATE_ADD(programmeEndDate,
INTERVAL - 1 * mnthsmargin MONTH) >= dt;
RETURN PMS;
END |