Content Comparison

...

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
   

...