79531682

Date: 2025-03-24 17:04:33
Score: 1.5
Natty:
Report link

Apologies, I wasn't clear so I created a debate around data validation, as I say my hands are tied to the branch system we are supplied, and being NHS, the options are limited.

Thanks to the suggestion from @ThomA, I used a windowed function Count to get what I was looking for, as mentioned from Alan, the second part was a simple join with a null check so here is the final query to obtain the list.

WITH cte AS (
SELECT PatientId, CardId, Surname, Forenames, DateOfBirth, PostCode, Branch,
COUNT(CardId) OVER(PARTITION BY Surname, ForeNames, DateOfBirth, PostCode) as [records]
FROM pmr.Patient
WHERE Branch = 9

)
SELECT cte.* FROM cte
LEFT JOIN pmr.[Session] s ON cte.PatientId = s.Patient AND cte.Branch = s.Branch
WHERE records > 1 AND s.Patient IS NULL
ORDER BY Surname, Forenames
Reasons:
  • Blacklisted phrase (0.5): Thanks
  • Long answer (-0.5):
  • Has code block (-0.5):
  • User mentioned (1): @ThomA
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: Colin-G-Davidson