You can't use MAX on one column and in SELECT and no aggregate function in remaining columns. You GROUP BY PATIENT_ID and it will be allowed.

You have to use INLINE view to accomplish this

SELECT CERT_PERIOD_ID, PATIENT_ID, CERTIFICATION_DATE, CERT_END_DATE, PAY_SOURCE_POINTER, IS_ACTIVE, CREATE_DATE, PERIOD_NO
FROM dbo.PTC_CERT_PERIOD as P1
JOIN
(SELECT PATIENT_ID, MAX(PERIOD_NO) AS MaxCertPeriod
FROM dbo.PTC_CERT_PERIOD
GROUP BY PATIENT_ID) as P2
ON P1.PATIENT_ID = P2.PATIENT_ID
AND P1.PERIOD_NO = P2.MaxCertPeriod