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
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
DAX
Respuestas
Estudiante
•respondió hace 3 años
Hola! Podrías usar los comandos WHERE o HAVING para aplicar esos condicionales.
Si la tabla de Excel del ejemplo es el resultado de tu consulta SQL, intenta agregar el condicional después del GROUP BY usando el comando HAVING.
Sería aquí
En esta clase vemos HAVING junto con GROUP BY: https://labs.datdata.com/sql/comandos en el minuto 44:27.
Pero si quieres aplicar el condicional antes de hacer los cálculos, usa WHERE. Lo vemos en esta clase: https://labs.datdata.com/sql/where
Un abrazo,
En esta clase vemos HAVING junto con GROUP BY: https://labs.datdata.com/sql/comandos en el minuto 44:27.
Pero si quieres aplicar el condicional antes de hacer los cálculos, usa WHERE. Lo vemos en esta clase: https://labs.datdata.com/sql/where
Un abrazo,
0
