Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

SQL to pull last obs values without duplicates

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

4 Posts

3 Users

0 Reactions

71 Views

RSS

cprice7

(@cprice7)

Posts: 71

Estimable Member

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

acantu

(@acantu)

Posts: 722

Prominent Member

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

cprice7

(@cprice7)

Posts: 71

Estimable Member

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

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

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

Share:

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Powered by wpForo Powered by wpForo version 2.4.0