Troubleshoot MIN()

Hello - looking for assistance on what I missed to make the MIN() not work in my script. Getting multiple results for Pract_ID 39909 and 39281.

SELECT DISTINCT 
  USER1.Username as Auditor
, USER2.Username as Specialist
, P.PRACT_ID as Pract_ID 
, ActiveProcess.StartDate as Opened
, ProcessName
, CASE WHEN(ProcessName like '%Initial%') then NULL else MIN(APF.NextReapptDate) END as Next_Reappt 

FROM Practitioner P

JOIN ActiveProcess on P.PRACT_ID = ActiveProcess.PRACT_ID
AND ActiveProcess.ProcessName like 'Peer Review%'

JOIN ActiveTask on ActiveTask.ActiveProcess_ID = ActiveProcess.ActiveProcess_ID
and ActiveTask.TaskName = 'Specialist Being Audited'

LEFT JOIN MSOW_USERS USER1 on USER1.UserID = ActiveTask.MSEmployeeAssigned

LEFT JOIN  MSOW_USERS USER2 on User2.UserID = ActiveTask.MSEmployeeActual

LEFT JOIN ActiveProcessFacilities APF on APF.ActiveProcess_ID=ActiveProcess.ActiveProcess_ID

GROUP BY P.PRACT_ID, USER1.Username, USER2.Username,  ActiveProcess.StartDate, ProcessName, ActiveTask.ActiveProcess_ID,  APF.FacCode

ORDER BY Opened
Auditor Specialist Pract_ID Opened ProcessName Next_Reappt
SS KO 40873 10/10/2023 PEER REVIEW INITIAL APPOINTMENT NULL
SS EJ 38926 10/17/2023 PEER REVIEW REAPPOINTMENT 11/29/2023
KW EJ 39909 10/24/2023 PEER REVIEW REAPPOINTMENT 6/29/2024
KW EJ 39909 10/24/2023 PEER REVIEW REAPPOINTMENT 11/29/2023
SS AS 39281 10/24/2023 PEER REVIEW REAPPOINTMENT 12/30/2023
SS AS 39281 10/24/2023 PEER REVIEW REAPPOINTMENT 9/29/2024
SS AC 40905 10/25/2023 PEER REVIEW INITIAL APPOINTMENT NULL
SS AC 13293 10/26/2023 PEER REVIEW INITIAL APPOINTMENT NULL
SS EJ 9913 10/26/2023 PEER REVIEW REAPPOINTMENT 12/30/2023
SS EJ 13052 10/26/2023 PEER REVIEW REAPPOINTMENT 12/30/2023

Hello,

Try something like this:

SELECT
	 src.Auditor 
	, src.Specialist
	, src.Pract_ID 
	, src.Opened
	, src.ProcessName
	, MIN(src.Next_Reappt) as Next_Reappt 
FROM
(
	SELECT  
	  USER1.Username as Auditor
	, USER2.Username as Specialist
	, P.PRACT_ID as Pract_ID 
	, ActiveProcess.StartDate as Opened
	, ProcessName
	, CASE WHEN(ProcessName like '%Initial%') then NULL else APF.NextReapptDate END as Next_Reappt 

	FROM Practitioner P

	JOIN ActiveProcess on P.PRACT_ID = ActiveProcess.PRACT_ID
	AND ActiveProcess.ProcessName like 'Peer Review%'

	JOIN ActiveTask on ActiveTask.ActiveProcess_ID = ActiveProcess.ActiveProcess_ID
	and ActiveTask.TaskName = 'Specialist Being Audited'

	LEFT JOIN MSOW_USERS USER1 on USER1.UserID = ActiveTask.MSEmployeeAssigned

	LEFT JOIN  MSOW_USERS USER2 on User2.UserID = ActiveTask.MSEmployeeActual

	LEFT JOIN ActiveProcessFacilities APF on APF.ActiveProcess_ID=ActiveProcess.ActiveProcess_ID
) as src
GROUP BY 
	src.Auditor 
	, src.Specialist
	, src.Pract_ID 
	, src.Opened
	, src.ProcessName
ORDER BY Opened

No reason to include DISTINCT with GROUP BY - that is the first problem. The other issues are:

, CASE WHEN(ProcessName like '%Initial%') then NULL else MIN(APF.NextReapptDate) END as Next_Reappt 

Wrap the CASE expression in MIN:

, MIN(CASE WHEN(ProcessName NOT LIKE '%Initial%') then APF.NextReapptDate END) as Next_Reappt 

And then remove the extra columns from the GROUP BY. You have included ActiveTask.ActiveProcess_ID and APF.FacCode in the grouping - and if a practice has multiple facility codes you will get a separate group for each one.

And finally - move the AND parts of each join to the where clause. They are not actually part of the join criteria and would be better in the where clause.

Thank you, this worked! When I had the MIN as originally written, MSSQL was indicating all the columns in SELECT had to be added so that is why I had those all in GROUP BY. But with MIN wrapping the CASE, I can remove from GROUP BY.