Assistance needed with MIN()

Other SQL Server Topics
1

I cannot get the MIN aggregate to work as desired. I want just the earliest date on staff from all applicable Faccodes. This script brings in all on staff dates.

Select DISTINCT P.PRACT_ID, PC.DocumentName, U.University_name, PC.Specialty, PC.Graduation_year
, DATEDIFF(MONTH, GapFromDate, GapThruDate) AS Program_Length_Months
, MIN(PF.Date_on_staff) as Date_On_Staff

FROM Practitioner P

JOIN Practitioner_Credentials PC ON P.PRACT_ID=PC.PRACT_ID
AND PC.DocumentName='Fellowship'
AND (PC.Specialty like '%Radio%' or PC.Specialty like '%Imag%')
AND Historical=0
AND ProgramCompleted=1

JOIN Universities U ON U.University_Record_No=PC.University_Record_No

JOIN Practitioner_Facilities PF ON PF.PRACT_ID=P.PRACT_ID
AND Current_status = 'Active'
AND FacCode IN ('HFH', 'JH', 'JHN', 'KW', 'MH', 'WH', 'WBH')

GROUP BY P.PRACT_ID, PC.DocumentName, U.University_name, PC.Specialty, PC.Graduation_year
,GapFromDate, GapThruDate, PF.Date_on_staff

2

You have PF.Date_on_staff in your GROUP BY clause, you need to remove it from there.

1 Like