Troubleshoot MIN()

Other SQL Server Topics
1

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
2

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
3

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.

4

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.