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
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.