SQL Query – adicionar condicion IF ?

Hola buen dia, necesito anadir esta condicion que uso en Excel a un SQL query que ya tengo establecido, pero no se donde agregarlo, me pueden ayudar?

esta es la condicion “IF” que se debe cumplir con los Flags de “Completed” y “Pending”

–          Completed = 1, Scheduled = 1, then Scheduled Flag = 0

–          Completed = 0, Scheduled = 1, then Scheduled Flag = 1

en Excel la formula es asi: (ver imagen adjunta)

este es el SQL Query al que tengo que agregarlo: 

SELECT DISTINCT a.RosterMonth
,a.SubscriberID
,a.EMRAccountNumber
,a.OriginalLastName
,a.OriginalFirstName
,a.OriginalDateOfBirth
,a.OriginalPayer
,e.Name as ‘Attributed_Provider_Name’
,f.PracticeShortName as ‘Group’
,f.Name as ‘Attributed_Clinic’
,a.OriginalLOB
,a.OriginalHomePhone
,a.OriginalCellPhone
,a.PatientID
,MAX(CASE WHEN (YEAR(b.appointmentdate) = YEAR(a.RosterMonth) AND b.VisitStatus like ‘checked-out’) THEN 1 ELSE 0 END) as ‘Completed’
,MAX(CASE WHEN (YEAR(b.appointmentdate) = YEAR(a.RosterMonth) AND b.VisitStatus like ‘pending’) THEN 1 ELSE 0 END) as ‘Pending’
,MAX(CASE WHEN (YEAR(b.appointmentdate) = YEAR(a.RosterMonth) AND b.VisitStatus in (‘checked-out’, ‘pending’)) THEN 0 ELSE 1 END) as ‘Needed’
FROM
(
SELECT DISTINCT z.*
FROM [cube].[FactPayersEnrollmentRoster] z
WHERE z.PossibleDuplicate = 0
AND z.IsForecastData = 0
AND z.OriginalIsForeCastData <> ‘Y’
AND z.LOBID = 6
AND z.RosterMonth like ‘2023-08-01’ –modify to latest roster period of active members
) a
LEFT JOIN
(
SELECT d.*
FROM [cube].FactVisits d
WHERE d.AppointmentDate between ‘2023-01-01’ AND ‘2023-12-31’–DOS YTD —
AND d.VisitStatus in (‘checked-out’, ‘pending’)
AND d.IsProviderVisit = ‘Y’
) b
ON a.PatientID = b.PatientId
LEFT JOIN [cube].vAttributedProvider e
ON a.AttributedProviderID = e.AttributedProviderID
LEFT JOIN [cube].vLocationAttributed f
on e.PrimaryLocationId = f.LocationId
GROUP BY a.RosterMonth
,a.SubscriberID
,a.EMRAccountNumber
,a.OriginalLastName
,a.OriginalFirstName
,a.OriginalDateOfBirth
,a.OriginalPayer
,e.Name
,f.PracticeShortName
,f.Name
,a.OriginalLOB
,a.OriginalHomePhone
,a.OriginalCellPhone
,a.PatientID
ORDER BY RosterMonth DESC