Notifications
Clear all
Topic starter
Anyone know of a SQL query that can be run to populate all patients registrations that have had an office visit since 01-01-2015 with the provider that they were last seen by? Can't seem to select the correct fields...
Posted : May 14, 2015 6:06 am
Run this first to see what you will be changing and to who:
SELECT First, Last, DoctorId AS Current_Primary_ID, (SELECT FIRSTNAME FROM USR WHERE doctorfacilityid = DoctorId AND STATUS = 'A') AS Current_Primary_First, (SELECT LASTNAME FROM USR WHERE doctorfacilityid = DoctorId AND STATUS = 'A') AS Current_Primary_Last , (SELECT FIRSTNAME FROM USR WHERE PVID = (SELECT TOP 1 USRID FROM DOCUMENT WHERE DOCTYPE = 1 ORDER BY dbo.Convert_ID_to_date(CLINICALDATE) DESC)) AS Last_Seen_First, (SELECT LASTNAME FROM USR WHERE PVID = (SELECT TOP 1 USRID FROM DOCUMENT WHERE DOCTYPE = 1 ORDER BY dbo.Convert_ID_to_date(CLINICALDATE) DESC)) AS Last_Seen_Last, (SELECT doctorfacilityid FROM USR WHERE PVID = (SELECT TOP 1 USRID FROM DOCUMENT WHERE DOCTYPE = 1 ORDER BY dbo.Convert_ID_to_date(CLINICALDATE) DESC)) AS Last_Seen_ID
FROM PatientProfile
WHERE (SELECT COUNT(*) FROM DOCUMENT WHERE DOCTYPE = 1 AND dbo.Convert_ID_to_date(CLINICALDATE) >= '01/01/2015' AND PID = PatientProfile.PID) > 0
If you are ok with the data, run this to process changes
UPDATE PatientProfile
SET DoctorId = (SELECT doctorfacilityid FROM USR WHERE PVID = (SELECT TOP 1 USRID FROM DOCUMENT WHERE DOCTYPE = 1 ORDER BY dbo.Convert_ID_to_date(CLINICALDATE) DESC))
WHERE (SELECT COUNT(*) FROM DOCUMENT WHERE DOCTYPE = 1 AND dbo.Convert_ID_to_date(CLINICALDATE) >= '01/01/2015' AND PID = PatientProfile.PID) > 0
Posted : May 14, 2015 6:27 am
SELECT x.patientid, x.visit, x.listname
FROM (SELECT pp.patientid, pv.visit, df.listname,
ROW_NUMBER() OVER (PARTITION BY pp.patientid ORDER BY pv.visit DESC) AS rank
FROM patientvisit pv
inner join patientprofile pp onpv.patientprofileid=pp.patientprofileid
inner join doctorfacility df onpv.DoctorId=df.doctorfacilityid
where visit >= '01-01-2015') x
WHERE x.rank= 1
Posted : May 14, 2015 6:38 am
