Code: Select all
SET @bwCodes = 'D0272,D0274,M0274';
SET @Date = curdate();
SELECT
aptDay.PatNum,
bwproc.CodeNum,
bwproc.ProcCode,
DATE(LastBW)
FROM (SELECT * FROM appointment a WHERE date(a.AptDateTime) = @Date) aptDay
/*Most recent @bwCode date for each patient*/
LEFT JOIN (
SELECT pl.PatNum, pl.CodeNum, pc.ProcCode, MAX(pl.ProcDate) as LastBW
FROM procedurelog pl
INNER JOIN procedurecode pc
ON pl.CodeNum = pc.CodeNum
AND pl.ProcStatus = 2 -- Complete
WHERE IF (LENGTH(@bwCodes) = 0, TRUE, FIND_IN_SET(pc.ProcCode,@bwCodes))
GROUP BY pl.PatNum
) bwproc
ON aptDay.PatNum = bwproc.PatNum
PatNum CodeNum ProcCode DATE(LastBW)
204 86 D0274 01/26/2024 12:00:00 AM
1053 86 D0274 12/19/2023 12:00:00 AM
1095 86 D0274 01/27/2024 12:00:00 AM
1026 86 D0274 01/27/2024 12:00:00 AM
If I group by ProcNum instead of pl.PatCode:
Code: Select all
SET @bwCodes = 'D0272,D0274,M0274';
SET @Date = curdate();
SELECT
aptDay.PatNum,
bwproc.CodeNum,
bwproc.ProcCode,
DATE(LastBW)
FROM (SELECT * FROM appointment a WHERE date(a.AptDateTime) = @Date) aptDay
/*Most recent @bwCode date for each patient*/
LEFT JOIN (
SELECT pl.PatNum, pl.CodeNum, pc.ProcCode, MAX(pl.ProcDate) as LastBW
FROM procedurelog pl
INNER JOIN procedurecode pc
ON pl.CodeNum = pc.CodeNum
AND pl.ProcStatus = 2 -- Complete
WHERE IF (LENGTH(@bwCodes) = 0, TRUE, FIND_IN_SET(pc.ProcCode,@bwCodes))
GROUP BY pl.ProcNum
) bwproc
ON aptDay.PatNum = bwproc.PatNum
/*GROUP BY aptDay.PatNum*/
I can find the correct ProcCode:
PatNum CodeNum ProcCode DATE(LastBW)
204 86 D0274 11/20/2021 12:00:00 AM
204 86 D0274 03/25/2023 12:00:00 AM
204 841 M0274 01/26/2024 12:00:00 AM
1053 86 D0274 12/19/2023 12:00:00 AM
1095 86 D0274 01/27/2024 12:00:00 AM
1026 86 D0274 01/27/2024 12:00:00 AM
But I only want the latest date for each patient (with correct procedure code).