Also, here is the SQL that I use in our QIE Interface, which runs every 5 minutes to make sure we do not miss anyone. As previously stated, it grabs any changes to the scheduled appointment and treats them as a possible reschedule and our receiving vendor just dismisses any duplicates that are not true changes.
select a.AppointmentsId, a.Duration, a.ApptStart, a.ApptStop, pp.PatientId, pp.Last, pp.First, pp.Middle, pp.Suffix, pp.Address1, pp.Phone1, pp.Phone1Type,
pp.Phone2, pp.Phone2Type, pp.Phone3, pp.Phone3Type, pp.Address2, pp.City, pp.State, pp.Zip, pp.Country, df.DotId, df.Last PLast, df.First PFirst,
df.Middle PMiddle, df.Phone1 PPhone1, pp.Birthdate, pp.Sex, l.ShortDescription, pp.SSN, pp.Country, ml.Code MaritalStatus, left(ml2.Code, 1) Race,
left(ml3.Code, 3) Ethnicity, pp.DeathDate, a.Notes, aty.Name ApptType, df2.ListName, df3.ListName Resource,
RTRIM(df4.Last+' '+ISNULL(df4.Suffix,'')) ProviderLast, df4.First ProviderFirst, df4.LoginUser ProviderID,
case
when pp.DeathDate is null
then null
else 'Y'
end as DeathInd
from Appointments a
join PatientProfile pp on CONVERT(char, a.OwnerId) = CONVERT(char, pp.PatientProfileId)
left join DoctorFacility df on a.LastModifiedBy = df.LoginUser
left join Language l on pp.LanguageId = l.LanguageId
left join MedLists ml on pp.MaritalStatusMId = ml.MedListsId
join (select a.ApptStart ApptStart, a.OwnerId
from Appointments a) q1 on a.OwnerId = q1.OwnerId and a.ApptStart = q1.ApptStart
left join (select min(pr.PatientRaceId) PRI, pr.PatientProfileId
from PatientRace pr
group by pr.PatientProfileID) q2 on pp.PatientProfileId = q2.PatientProfileId
left join (select min(pe.PatientEthnicityId) PEI, pe.PatientProfileId
from PatientEthnicity pe
group by pe.PatientProfileId) q3 on pp.PatientProfileId = q3.PatientProfileId
left join PatientRace pr on q2.PRI = pr.PatientRaceId
left join MedLists ml2 on pr.PatientRaceMid = ml2.MedListsId
left join PatientEthnicity pe on q3.PEI = pe.PatientEthnicityId
left join MedLists ml3 on pe.PatientEthnicityMid = ml3.MedListsId
left join ApptType aty on a.ApptTypeId = aty.ApptTypeId
left join DoctorFacility df2 on a.FacilityId = df2.DoctorFacilityId
left join DoctorFacility df3 on a.ResourceId = df3.DoctorFacilityId
left join DoctorFacility df4 on a.DoctorId = df4.DoctorFacilityId
where (ml.TableName = 'MaritalStatus' or ml.TableName is null)
and (ml2.TableName = 'Race' or ml2.TableName is null)
and (ml3.TableName = 'Ethnicity' or ml3.TableName is null)
and a.status not in ('Arrived','Absent','Completed','Deceased','In a Room','Insurance Issues-Cancelled','No show','MICARE PHONE F/U - no text/no reminder','LINKED APPT - no text/no reminder',
'PROVIDER OUT - no text/no reminder','NO REMINDER CALL, NO TEXTS','Rescheduled','Operator error','Patient left w/o being seen','Provider running behind','Patient hospitalized','Transported by cab/ taxi','Not Due','Not Eligible','Discharged')
and a.Status not like 'Cancel%'
and a.LastModified >= DATEADD(mi, -10, GETDATE())
and a.Created < a.LastModified
Posted : October 18, 2019 2:24 am