Notifications
Clear all
Topic starter
Hi,
Has anyone written a sql script to get the first visit date of a patient?
IE patient has multiple visits what is needed is the first visit (Not the create date in patient profile)
Thanks
Posted : February 18, 2016 8:07 am
Hello, there are a few things you can look at:
If you are looking for the first office visit for a patient you can use this:
For C-EMR:
SELECT CONVERT_ID_TO_DATE(CLINICALDATE) AS FIRSTDOCDATE
FROM DOCUMENT
WHERE PID IN (
SELECT PID
FROM PERSON
WHERE PATIENTID = 'REPLACE WITH PATIENT ID') AND DOCTYPE = 1 AND ROWNUM = 1
ORDER BY CONVERT_ID_TO_DATE(CLINICALDATE) DESC;
For CPS:
SELECT TOP 1 dbo.Convert_ID_to_date(CLINICALDATE)
FROM DOCUMENT
WHERE PID IN (
SELECT PID
FROM PatientProfile
WHERE PatientId = 'REPLACE WITH PATIENT ID') AND DOCTYPE = 1
ORDER BY dbo.Convert_ID_to_date(CLINICALDATE) DESC
if you are looking for the first appointment, you can use this:
For C-EMR:
SELECT APPTDATE
FROM APPT
WHERE PID IN (
SELECT PID
FROM PERSON
WHERE PATIENTID = 'REPLACE WITH PATIENT ID') AND APPTSTATUS NOT IN (1,2) AND ROWNUM = 1
ORDER BY APPTDATE DESC;
For CPS:
SELECT TOP 1 ApptStart
FROM Appointments
WHERE OwnerId IN (
SELECT PatientProfileId
FROM PatientProfile
WHERE PatientId = 'REPLACE WITH PATIENT ID') AND
Canceled IS NULL
ORDER BY ApptStart DESC
Posted : February 18, 2016 8:15 am
