Content Comparison

...

1. TraineeProfile: add linkedProgrammeMembership[]: {uuid, when, how, metrics} to placement subdocument. The actual details of the PM are already held in the programmeMembership[] subdocuments.

2. tis-trainee-sync: add placementProgrammeMembership collection to be synced from TIS, and triggering re-enrichment of related placements. For the first phase, the only sync will be the Placement-ProgrammeMembership-Link table from TIS. When an INSERT/UPDATE/DELETE record arrives in tis-trainee-sync for that table we would need to update the sync collection, re-enrich the related placement and update the trainee profile accordingly.
   In addition, send this new trainee profile content to the NDW.
   In phase 1, the new linkedProgrammeMembership data should not affect TSS credentials, forms, notifications or actions,

...

Notes:

1. Post linkage (not reliable):
   Sometimes placements ‘borrow’ post from another programme because otherwise post management becomes laborious, e.g. Emergency Medicine Higher → EM run-through; GP using Obstetrics etc. in hospital placement.

...

3. When the notifications pilot was run, we had a slightly different rule to identify placements that belonged to pilot programme memberships. This rule had a similar rate of ambiguous / missing matches (and in the context we didn't mind, since it was intended purely to identify if there was at least one programme membership that the placement might belong to that was in the pilot, rather than identify that PM unambiguously).

   New rule:
   - placement start date between PM start and end dates OR
   - placement start date no more than 13 days before the PM start date AND placement end date no later than PM end date

   The rule we used for the pilot logic:
   - placement start date not before 1st of month of PM start date AND placement start date on no later than PM end date
   Comparison:
   SQL new rule (example for no matches, 2023):
   SELECT
count(*)
-- pl.id, pl.dateFrom, pl.dateTo, matches, minpmuuid, pmmin.programmeStartDate, pmmin.programmeEndDate, maxpmuuid, pmmax.programmeStartDate, pmmax.programmeEndDate
FROM tcs.Placement pl JOIN
(
SELECT id, sum(ismatch) as matches, min(uuid) as minpmuuid, max(uuid) as maxpmuuid FROM
(
SELECT pl.id, pm.uuid, if((pl.dateFrom >= pm.programmeStartDate AND pl.dateFrom <= pm.programmeEndDate)
OR (date_add(pl.dateFrom, interval 13 day) >= pm.programmeStartDate AND pl.dateTo <= pm.programmeEndDate), 1, 0) AS isMatch FROM
tcs.Placement pl
JOIN tcs.ProgrammeMembership pm ON pm.personId = pl.traineeId
WHERE (pl.dateFrom >= "2022-01-01" AND pl.dateFrom < "2023-01-01")
) plpm_match
GROUP BY id
) plsummary ON pl.id = plsummary.id
JOIN tcs.ProgrammeMembership pmmin ON pmmin.uuid = plsummary.minpmuuid
JOIN tcs.ProgrammeMembership pmmax ON pmmax.uuid = plsummary.maxpmuuid
WHERE matches = 0
   Pilot rule (example for no matches, 2023):
   SELECT
count(*)
-- pl.id, pl.dateFrom, pl.dateTo, matches, minpmuuid, pmmin.programmeStartDate, pmmin.programmeEndDate, maxpmuuid, pmmax.programmeStartDate, pmmax.programmeEndDate
FROM tcs.Placement pl JOIN
(
SELECT id, sum(ismatch) as matches, min(uuid) as minpmuuid, max(uuid) as maxpmuuid FROM
(
SELECT pl.id, pm.uuid, if((pl.dateFrom >= CAST(DATE_FORMAT(pm.programmeStartDate ,'%Y-%m-01') as DATE) AND pl.dateFrom <= pm.programmeEndDate), 1, 0) AS isMatch FROM
tcs.Placement pl
JOIN tcs.ProgrammeMembership pm ON pm.personId = pl.traineeId
WHERE (pl.dateFrom >= "2022-01-01" AND pl.dateFrom < "2023-01-01")
) plpm_match
GROUP BY id
) plsummary ON pl.id = plsummary.id
JOIN tcs.ProgrammeMembership pmmin ON pmmin.uuid = plsummary.minpmuuid
JOIN tcs.ProgrammeMembership pmmax ON pmmax.uuid = plsummary.maxpmuuid
WHERE matches = 0
   

Note that the table above excludes placements for the 2-3% of trainees who have no programme memberships set up. Revised estimates for recent years including different linking methods are summarised below: