SQL help - last ProcDate for each PatNum

[Floss]

I have the following query:

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

It correctly gets the last bitewing date for each patient, but the CodeNum and ProcCode are incorrect:
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).

[Floss]

Nevermind, I fixed it. Order by ProcDate, then group by PatNum:

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 ORDER BY pl.ProcDate DESC)
	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 bwproc.PatNum