Notifications
Clear all
General Discussion
3
Posts
3
Users
0
Reactions
67
Views
Topic starter
I'm trying to pull in the name of the person that entered blood pressure (bpdia and bpsys observations.) Also need to pull in the physician that saw the patient on the day those values were entered.
Anyone have any suggestions on how to do that in Crystal Reports.... not sure the DB tables to use or how to create the formula.
Thank you,
Posted : February 11, 2016 9:00 am
You can use the following SQL scripts, hopefully this helps:
For Oracle: (C-EMR)
SELECT DISTINCT (SELECT RTRIM(FIRSTNAME || ' ' || MIDDLENAME || ' ' || LASTNAME) FROM USR WHERE PVID = OBS.USRID) AS ENTERED_BY, (SELECT RTRIM(FIRSTNAME || ' ' || MIDDLENAME || ' ' || LASTNAME) FROM USR WHERE PVID = (SELECT USRID FROM DOCUMENT WHERE SDID = OBS.SDID)) AS SEEN_BY, (SELECT OBSDATE FROM OBS OB WHERE SDID = OBS.SDID AND HDID = OBS.HDID AND RowNum = 1) AS DATE_ENTERED,
(SELECT OBSVALUE FROM OBS OB1 WHERE HDID IN (54,2877,2879,2881,5247,5249,5909,21682,21683,21684,26151,26607,52001,92415) AND SDID = OBS.SDID AND RowNum = 1) AS BPSYS,
(SELECT OBSVALUE FROM OBS OB2 WHERE HDID IN (53,2878,2880,2882,5248,5250,5912,21679,21680,21681,26155,26967,51993,92416,300065) AND SDID = OBS.SDID AND RowNum = 1) AS BPDIA,
(SELECT SEARCHNAME FROM PERSON WHERE PID = OBS.PID) AS PATIENT
FROM OBS
WHERE HDID IN (53,2878,2880,2882,5248,5250,5912,21679,21680,21681,26155,26967,51993,92416,300065,54,2877,2879,2881,5247,5249,5909,21682,21683,21684,26151,26607,52001,92415) AND SDID = [INPUT SDID FOR VISIT HERE]
For MS SQL: (CPS)
SELECT DISTINCT (SELECT dbo.FormatName('',FIRSTNAME,MIDDLENAME,LASTNAME,'') FROM USR WHERE PVID = OBS.USRID) AS ENTERED_BY, (SELECT dbo.FormatName('',FIRSTNAME,MIDDLENAME,LASTNAME,'') FROM USR WHERE PVID = (SELECT TOP 1 USRID FROM DOCUMENT WHERE SDID = OBS.SDID)) AS SEEN_BY, (SELECT OBSDATE FROM OBS OB WHERE SDID = OBS.SDID AND HDID = OBS.HDID) AS DATE_ENTERED,
(SELECT TOP 1 OBSVALUE FROM OBS OB1 WHERE HDID IN (54,2877,2879,2881,5247,5249,5909,21682,21683,21684,26151,26607,52001,92415) AND SDID = OBS.SDID) AS BPSYS,
(SELECT TOP 1 OBSVALUE FROM OBS OB2 WHERE HDID IN (53,2878,2880,2882,5248,5250,5912,21679,21680,21681,26155,26967,51993,92416,300065) AND SDID = OBS.SDID) AS BPDIA,
(SELECT dbo.FormatName(Prefix,FIRST,MIDDLE,LAST,Suffix) FROM PatientProfile WHERE PID = OBS.PID) AS PATIENT
FROM OBS
WHERE HDID IN (53,2878,2880,2882,5248,5250,5912,21679,21680,21681,26155,26967,51993,92416,300065,54,2877,2879,2881,5247,5249,5909,21682,21683,21684,26151,26607,52001,92415) AND SDID = [INPUT SDID FOR VISIT HERE]
Posted : February 11, 2016 10:56 am
Have you resolved this issue?
Posted : June 9, 2016 6:43 am
