Business rules

1. A programme membership may be linked to zero [one?] or more placements.

2. A placement will be linked to at most [exactly?] one programme membership.

3. [There may be circumstances where a placement is linked to more than one programme membership. Specific examples TBC.]

4. The linking of placements to programme memberships will be managed by LOs, not trainees.

5. Some historic placements / programme memberships may need to remain unlinked (dirty data, ambiguity).

6. Which placements are eligible to be linked to a programme membership are those for the same trainee where the placement post is the same as the programme post, i.e.:

Ambiguity in this linkage can be seen here: https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgcGwuaWQsIGNvdW50KCopIFxyXG5GUk9NIHRjcy5Qcm9ncmFtbWVNZW1iZXJzaGlwIHBtIFxyXG5KT0lOIHRjcy5Qcm9ncmFtbWUgcHJnIE9OIHBtLnByb2dyYW1tZUlkID0gcHJnLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZVBvc3QgcHAgT04gcHJnLmlkID0gcHAucHJvZ3JhbW1lSWRcclxuSk9JTiB0Y3MuUGxhY2VtZW50IHBsIE9OIHBsLnBvc3RJZCA9IHBwLnBvc3RJZCBBTkQgcG0ucGVyc29uSWQgPSBwbC50cmFpbmVlSWRcclxuV0hFUkUgcG0ucHJvZ3JhbW1lU3RhcnREYXRlID4gJzIwMjQtMDEtMDEnXHJcbkdST1VQIEJZIHBsLmlkXHJcbkhBVklORyBjb3VudCgqKSA-IDE7IiwidGVtcGxhdGUtdGFncyI6e319LCJkYXRhYmFzZSI6MzZ9LCJkaXNwbGF5IjoidGFibGUiLCJ2aXN1YWxpemF0aW9uX3NldHRpbmdzIjp7fX0=
select pl.id, count()
FROM tcs.ProgrammeMembership pm
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id
HAVING count() > 1;

Enhanced placement eligibility criteria (TBC): specialties must also match, i.e.:

Ambiguity in this linkage can be seen here:
https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgaWQsIGNvdW50KCopXHJcbkZST00gKFxyXG5TRUxFQ1QgcGwuaWQsIHBtLnV1aWQgRlJPTVxyXG50Y3MuUHJvZ3JhbW1lTWVtYmVyc2hpcCBwbSBcclxuSk9JTiB0Y3MuQ3VycmljdWx1bU1lbWJlcnNoaXAgY20gT04gcG0udXVpZCA9IGNtLnByb2dyYW1tZU1lbWJlcnNoaXBVdWlkXHJcbkpPSU4gdGNzLkN1cnJpY3VsdW0gYyBPTiBjbS5jdXJyaWN1bHVtSWQgPSBjLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZSBwcmcgT04gcG0ucHJvZ3JhbW1lSWQgPSBwcmcuaWRcclxuSk9JTiB0Y3MuUHJvZ3JhbW1lUG9zdCBwcCBPTiBwcmcuaWQgPSBwcC5wcm9ncmFtbWVJZFxyXG5KT0lOIHRjcy5Qb3N0U3BlY2lhbHR5IHBzIE9OIHBwLnBvc3RJZCA9IHBzLnBvc3RJZCBBTkQgYy5zcGVjaWFsdHlJZCA9IHBzLnNwZWNpYWx0eUlkXHJcbkpPSU4gdGNzLlBsYWNlbWVudCBwbCBPTiBwbC5wb3N0SWQgPSBwcC5wb3N0SWQgQU5EIHBtLnBlcnNvbklkID0gcGwudHJhaW5lZUlkXHJcbldIRVJFIHBtLnByb2dyYW1tZVN0YXJ0RGF0ZSA-ICcyMDI0LTAxLTAxJ1xyXG5HUk9VUCBCWSBwbC5pZCwgcG0udXVpZFxyXG4pIHhcclxuR1JPVVAgQlkgaWRcclxuSEFWSU5HIGNvdW50KCopID4gMSIsInRlbXBsYXRlLXRhZ3MiOnt9fSwiZGF0YWJhc2UiOjM2fSwiZGlzcGxheSI6InRhYmxlIiwidmlzdWFsaXphdGlvbl9zZXR0aW5ncyI6e319
select id, count()
FROM (
SELECT pl.id, pm.uuid FROM
tcs.ProgrammeMembership pm
JOIN tcs.CurriculumMembership cm ON pm.uuid = cm.programmeMembershipUuid
JOIN tcs.Curriculum c ON cm.curriculumId = c.id
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.PostSpecialty ps ON pp.postId = ps.postId AND c.specialtyId = ps.specialtyId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id, pm.uuid
) x
GROUP BY id
HAVING count() > 1

TBC: do postSpecialtyType values impact this?

7. Even with these constraints, ambiguities arise where a placement could be linked to more than one programme membership. Start and end dates may be useful in resolving these, but these may be less reliable.