Notifications
Clear all
Topic starter
Hello all,
I need a script to show patients seen over the age of 65 yrs by provider from 7/1/2015 to 7/1/ 2017.
The columns that i need is patient name, DOB, visit date.
Appreciate any help on this!!
Posted : August 17, 2017 9:45 am
Here you go:
SELECT dbo.FormatName(PP.Prefix,PP.First,PP.Middle,PP.Last,PP.Suffix) [Patient Name], PP.PatientId, DATEDIFF(hour,PP.Birthdate,GETDATE())/8766 [AGE], CONVERT(VARCHAR(10),PP.Birthdate,101) [DOB], dbo.Convert_ID_to_date(CLINICALDATE) [Visit Date]
FROM PatientProfile PP INNER JOIN
DOCUMENT DOC ON PP.PID = DOC.PID
WHERE DATEDIFF(hour,PP.Birthdate,GETDATE())/8766 > 65 AND PatientStatusMId = -900
AND dbo.Convert_ID_to_date(CLINICALDATE) BETWEEN '7/1/2015' AND '7/1/2017' AND
DOC.DOCTYPE = 1
Posted : August 17, 2017 10:26 am
Topic starter
Thanks!!
How do i plug in By Provider?
Can I join in USR table? I prefer doing it by loginname. Let me know. Thanks!
Posted : August 17, 2017 10:54 am
Add this to the bottom of the script
AND DOC.USRID IN (SELECT PVID FROM USR WHERE LOGINNAME = 'providerloginhere')
Posted : August 17, 2017 11:05 am
Topic starter
I am getting that error
Posted : August 17, 2017 11:13 am
Topic starter
I dont think i have Oracle. I am getting an error with that script too. I think the last script was the right one but its missing something simple maybe, bracket or parenthesis?
Posted : August 17, 2017 2:05 pm
Oh you have an extra INNER JOIN, here is the script to fix yours:
SELECT dbo.FormatName(PP.Prefix,PP.First,PP.Middle,PP.Last,PP.Suffix) [Patient Name], PP.PatientId, DATEDIFF(hour,PP.Birthdate,GETDATE())/8766 [AGE], CONVERT(VARCHAR(10),PP.Birthdate,101) [DOB], dbo.Convert_ID_to_date(CLINICALDATE) [Visit Date]
FROM PatientProfile PP INNER JOIN
DOCUMENT DOC ON PP.PID = DOC.PID
WHERE DATEDIFF(hour,PP.Birthdate,GETDATE())/8766 > 65 AND PatientStatusMId = -900
AND dbo.Convert_ID_to_date(CLINICALDATE) BETWEEN '7/1/2015' AND '7/1/2017' AND
DOC.DOCTYPE = 1 AND DOC.USRID IN (SELECT PVID FROM USR WHERE LOGINNAME = 'ej.jnoram')
if this script works for you, please mark this as the answer to your question 🙂
Posted : August 17, 2017 2:08 pm
Topic starter
I think i got it figured out...
SELECT PP.[First], PP.[Last], PP.Birthdate, PP.Sex, dbo.Convert_ID_to_date(CLINICALDATE)
FROM PatientProfile PP INNER JOIN
DOCUMENT DOC ON PP.PID = DOC.PID
WHERE DATEDIFF(hour,PP.Birthdate,GETDATE())/8766 > 65 AND PatientStatusMId = -900
AND dbo.Convert_ID_to_date(CLINICALDATE) BETWEEN '7/1/2015' AND '7/1/2017' AND
DOC.DOCTYPE = 1
AND DOC.USRID IN (SELECT PVID FROM USR WHERE LOGINNAME = 'ej.jnoram')
Posted : August 17, 2017 2:16 pm
Great, glad to be of service.
Posted : August 17, 2017 2:19 pm
Topic starter
Thank you for your help!
Posted : August 17, 2017 2:22 pm
