'SUBQUERY NOT IN STILL SHOWS UP
I am trying to exclude the appointment_key of the patient's first appointment date from the main query to explore the patient data after the first appointment. I created a subquery for the minimum appointment key table that goes to in Where clause.
To confirm this logic, APT.AppointmentKey = 42972, one of the patient's appointment keys, will not be populated since this is in the subquery. However, the data is in the output.
Could you please help me out why it is still returning?
SELECT DISTINCT APT.PatientKey, CONCAT(PAT.PatientFirstName,' ',PAT.PatientLastName) AS "Patient Name", ISNULL(PAT.Email,'') as "Email", CONCAT('(',ISNULL(Pat.mareacode,''),') - ',ISNULL(pat.Mphone,'')) AS "Patient Cell Phone", PAT.AccountNumber, APT.AppointmentStatus, APT.AppointmentDate, APT.AppointmentKey
FROM Appointment APT
LEFT JOIN Patient PAT ON APT.PatientKey = PAT.PatientKey AND PAT.SourceKey = 1 AND PAT.DeleteFlag = 0
LEFT JOIN Encounter ENC ON ENC.EncounterKey = APT.EncounterKey AND ENC.SourceKey = 1 AND ENC.DeleteFlag = 0
LEFT JOIN PlaceOfService POS ON POS.PlaceOfServiceKey = APT.PlaceOfServiceKey AND POS.SourceKey = 1 AND POS.DeleteFlag = 0
WHERE APT.SourceKey = 1
AND APT.AppointmentKey
NOT IN
(
SELECT MinApptKey.MIN_ApptKey
FROM
(SELECT MIN(INNER_APT.AppointmentDate) MIN_DATE, MIN(INNER_APT.AppointmentKey) MIN_ApptKey, PAT.AccountNumber
FROM Appointment INNER_APT
LEFT JOIN Patient PAT ON PAT.PatientKey = INNER_APT.PatientKey AND PAT.SourceKey = 1 AND PAT.DeleteFlag = 0
LEFT JOIN Encounter ENC ON ENC.EncounterKey = INNER_APT.EncounterKey AND ENC.DeleteFlag = 0 AND ENC.SourceKey = 1
LEFT JOIN PlaceOfService POS ON POS.PlaceOfServiceKey = INNER_APT.PlaceOfServiceKey AND POS.DeleteFlag = 0 AND POS.SourceKey = 1
WHERE INNER_APT.SourceKey = 1 AND YEAR(INNER_APT.AppointmentDate) >= 2020 AND ENC.EncounterCode IN ('ALC','ALGY','ALSH')
AND (POS.PlaceOfServiceDesc LIKE ('CARE CENTER 8%') OR POS.PlaceOfServiceDesc LIKE ('CARE CENTER 10%') OR POS.PlaceOfServiceDesc LIKE ('CARE CENTER 15%'))
GROUP BY INNER_APT.PatientKey, PAT.AccountNumber) MinApptKey
) -- This MinApptKey subquery will exclude the first appointment date.
AND ENC.EncounterCode IN ('ALC','ALGY','ALSH')
AND APT.AppointmentStatus = 'ARR'
AND YEAR(APT.AppointmentDate) >= 2020
AND (POS.PlaceOfServiceDesc LIKE ('CARE CENTER 8%') OR POS.PlaceOfServiceDesc LIKE ('CARE CENTER 10%') OR POS.PlaceOfServiceDesc LIKE ('CARE CENTER 15%'))
AND APT.AppointmentKey = 42972
ORDER BY APT.PatientKey
Thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
