Notifications
Clear all
Topic starter
I am working on a SQL query that will display multiple obs term values as columns. I almost have it complete, but I would like to make it only pull the requested obs values from the last Location Change document document, not every Location Change Document Type as it does now, so every patient ID (PID) will be there only once. Can anybody please help?
Here's what I have so far:
SELECT [pid], FacilityObs, Section, Block, Cell, Bed, DocumentDate
FROM (SELECT dbo.Convert_ID_to_date(DOC.CLINICALDATE) AS DocumentDate, PP.PId AS [pid],
(SELECT OBSVALUE
FROM dbo.OBS
WHERE (SDID = DOC.SDID) AND (HDID =
(SELECT HDID
FROM dbo.OBSHEAD
WHERE (NAME = 'FacilityName')))) AS FacilityObs,
(SELECT OBSVALUE
FROM dbo.OBS AS OBS_4
WHERE (SDID = DOC.SDID) AND (HDID =
(SELECT HDID
FROM dbo.OBSHEAD AS OBSHEAD_4
WHERE (NAME = 'IMLOCSECTION')))) AS Section,
(SELECT OBSVALUE
FROM dbo.OBS AS OBS_3
WHERE (SDID = DOC.SDID) AND (HDID =
(SELECT HDID
FROM dbo.OBSHEAD AS OBSHEAD_3
WHERE (NAME = 'IMLOCBLOCK')))) AS Block,
(SELECT OBSVALUE
FROM dbo.OBS AS OBS_2
WHERE (SDID = DOC.SDID) AND (HDID =
(SELECT HDID
FROM dbo.OBSHEAD AS OBSHEAD_2
WHERE (NAME = 'IMLOCCELL')))) AS Cell,
(SELECT OBSVALUE
FROM dbo.OBS AS OBS_1
WHERE (SDID = DOC.SDID) AND (HDID =
(SELECT HDID
FROM dbo.OBSHEAD AS OBSHEAD_1
WHERE (NAME = 'IMLOCBED')))) AS Bed
FROM dbo.[DOCUMENT] AS DOC INNER JOIN
dbo.DOCTYPES AS DT ON DOC.DOCTYPE = DT.DTID LEFT OUTER JOIN
dbo.PatientProfile AS PP ON DOC.PID = PP.PId LEFT OUTER JOIN
dbo.LOCREG ON DOC.LOCOFCARE = dbo.LOCREG.LOCID
WHERE (DT.DESCRIPTION = 'Location Change')) AS PRE
Posted : February 15, 2018 12:05 pm
Here you go, just need to put a row no. and partition by pid and order by the clinical date, and then get the newest one from your subquery, code is below:
SELECT [pid], FacilityObs, Section, Block, Cell, Bed, DocumentDate
FROM (
SELECT dbo.Convert_ID_to_date(DOC.CLINICALDATE) AS DocumentDate, PP.PId AS [pid],
(SELECT OBSVALUE FROM dbo.OBS WHERE (SDID = DOC.SDID) AND (HDID = (SELECT HDID FROM dbo.OBSHEAD WHERE (NAME ='FacilityName')))) AS FacilityObs,
(SELECT OBSVALUE FROM dbo.OBS AS OBS_4 WHERE (SDID = DOC.SDID) AND (HDID = (SELECT HDID FROM dbo.OBSHEAD AS OBSHEAD_4 WHERE (NAME = 'IMLOCSECTION')))) AS Section,
(SELECT OBSVALUE FROM dbo.OBS AS OBS_3 WHERE (SDID = DOC.SDID) AND (HDID = (SELECT HDID FROM dbo.OBSHEAD AS OBSHEAD_3 WHERE (NAME = 'IMLOCBLOCK')))) AS Block,
(SELECT OBSVALUE FROM dbo.OBS AS OBS_2 WHERE (SDID = DOC.SDID) AND (HDID = (SELECT HDID FROM dbo.OBSHEAD AS OBSHEAD_2 WHERE (NAME = 'IMLOCCELL')))) AS Cell,
(SELECT OBSVALUE FROM dbo.OBS AS OBS_1 WHERE (SDID = DOC.SDID) AND (HDID = (SELECT HDID FROM dbo.OBSHEAD AS OBSHEAD_1 WHERE (NAME = 'IMLOCBED')))) AS Bed,
ROW_NUMBER() OVER (PARTITION BY DOC.PID ORDER BY DOC.CLINICALDATE DESC) DUP
FROM dbo.[DOCUMENT] AS DOC INNER JOIN
dbo.DOCTYPES AS DT ON DOC.DOCTYPE = DT.DTID LEFT OUTER JOIN
dbo.PatientProfile AS PP ON DOC.PID = PP.PId LEFT OUTER JOIN
dbo.LOCREG ON DOC.LOCOFCARE = dbo.LOCREG.LOCID
WHERE (DT.DESCRIPTION = 'Location Change')
) AS PRE
WHERE DUP = 1
Posted : February 15, 2018 12:36 pm
Topic starter
Arnold - I didn't even know that existed. You are amazing. It works like charm. Thanks!
Posted : February 16, 2018 8:13 am
I created a function for oracle that might help you in future reports. It grabs the latest observation value that is within a specified date range (if one is specified). It works similarly to OBSANY() in MEL.
It is run like this GET_OBS(PID,'OBSERVATIONTERM',STARTDATE,ENDDATE) where startdate and/or end date can be left out if not needed.
It works well when you have your SQL already filtering a list of patients, (like adding a column to the select clause. It does not work so well in the where clause unless you have other filters on the data. I use it all of the time to create reports for measures/protocols.
create or replace FUNCTION GET_OBS(PID_IN IN NUMBER, OBSNAME_IN IN VARCHAR2, BEGIN_DATE IN DATE DEFAULT TO_DATE('1960-01-01', 'YYYY-MM-DD'), END_DATE IN DATE DEFAULT TO_DATE('2460-01-01', 'YYYY-MM-DD'))
RETURN VARCHAR2
IS OBSVALUE_OUT VARCHAR2(2000);
BEGIN
SELECT OBS2.OBSVALUE INTO OBSVALUE_OUT FROM OBS OBS2
INNER JOIN
(SELECT OBS1.PID, OBS1.HDID, MAX(OBS1.OBSDATE) AS OBSDATE FROM OBS OBS1
INNER JOIN OBSHEAD OH1 ON OBS1.HDID = OH1.HDID
WHERE OBS1.CHANGE IN (2,6) AND OBS1.XID = 1e+35
AND OH1.NAME = OBSNAME_IN AND OBS1.PID = PID_IN
AND OBS1.OBSDATE >= BEGIN_DATE AND OBS1.OBSDATE < END_DATE + 1
GROUP BY OBS1.PID, OBS1.HDID
) SUBQ1 ON OBS2.PID = SUBQ1.PID AND OBS2.HDID = SUBQ1.HDID AND OBS2.OBSDATE = SUBQ1.OBSDATE
AND OBS2.XID = 1e+35 AND OBS2.CHANGE IN (2,6) AND ROWNUM < 2;
RETURN(OBSVALUE_OUT);
END;
Posted : February 19, 2018 10:12 am
