SELECT
PPPS.PatientPhysicianPatientSurveyId,
P.ProcedureId,
PS.SurveyId,
SPD.SurveyPhysicianDurationId AS DurationId,
SPDN.SurveyPhysicianDurationNotificationId AS NotificationId,
SPO.Ordinal,
DATEADD(HOUR, SPDN.Hours_Before_Notification, PPPS.DueDate) AS NotificationDate,
PA.Email AS Recipient,
AG.AnatomyGroupName + ' SURGERY QUESTIONNAIRE' +
	CASE
		WHEN
			SPDN.Hours_Before_Notification <> (
				SELECT MIN(SPDN2.Hours_Before_Notification)
				FROM Survey_Physician_Duration_Notification SPDN2
				WHERE SPDN2.SurveyPhysicianDurationId = SPD.SurveyPhysicianDurationId
			)
		THEN ' *REMINDER*'
		ELSE ''
	END AS [Subject],
CASE WHEN PPPS.ShowDate > CURRENT_TIMESTAMP THEN 1 ELSE 0 END AS IsFutureNotification
FROM Patient_Physician_PatientSurvey PPPS
JOIN dbo.Survey_Physician_Duration SPD ON SPD.SurveyPhysicianDurationId = PPPS.SurveyPhysicianDurationId AND SPD.IsActive=1
JOIN dbo.Survey_Physician SP ON SP.SurveyPhysicianId = SPD.SurveyPhysicianId AND SP.IsActive=1 AND SP.FilledByPatient = 1 AND SP.NotificationTemplate IS NOT NULL AND SP.SendSurveyNotification=1
JOIN dbo.PatientSurvey PS ON PS.SurveyId = SP.SurveyId AND PS.IsActive=1
JOIN dbo.Survey_Physician_Duration_Notification SPDN ON SPDN.SurveyPhysicianDurationId = SPD.SurveyPhysicianDurationId
JOIN dbo.Survey_Physician_Ordinal SPO ON SPO.SurveyId = SP.SurveyId AND (SPO.PhysicianId = SP.PhysicianId OR SPO.ClientId = SP.ClientId)
JOIN dbo.[Procedure] P ON P.ProcedureId = PPPS.ProcedureId AND P.IsActive = 1
JOIN dbo.Surgery S ON S.SurgeryId = P.SurgeryId AND S.IsActive = 1 AND S.SurgeryStatusId <> 5
JOIN dbo.AnatomyGroup AG ON AG.AnatomyGroupId = S.AnatomyGroupId AND SPO.AnatomyGroupId = S.AnatomyGroupId
JOIN dbo.Patient PA ON PA.PatientId = P.PatientId AND PA.ReceivesEmail=1 AND PA.Email IS NOT NULL
WHERE
PPPS.DateFinished IS NULL
AND NOT EXISTS(
SELECT TOP 1 1 FROM
	OutboundEmail OE
	WHERE OE.DurationId = PPPS.SurveyPhysicianDurationId
	AND OE.NotificationId = SPDN.SurveyPhysicianDurationNotificationId
	AND OE.PatientId = PA.PatientId
)
AND P.PatientId in (254014,254013,254016)
AND DATEADD(HOUR, SPDN.Hours_Before_Notification, PPPS.DueDate) >= GETDATE()