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

Multiple HDID's from the same Encounter From in SQL

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

3 Posts

2 Users

0 Reactions

75 Views

RSS

gshutter

(@gshutter)

Posts: 5

Active Member

Topic starter

I'm building a SQL report using multiple HDID's with obsvalues from the same encounter form, below I posted my code, I am having trouble figuring out how to pull NULLs from the rest of the HDID's and just pull each line for ML.RPTOBS.

SELECT        PERSON.SEARCHNAME, PERSON.ADDRESS1, PERSON.CITY, PERSON.PSTATUS, ML.RPTOBS.OBSVALUE AS AdminDate, RPTOBS_1.OBSVALUE AS DischargeDate, RPTOBS_2.OBSVALUE AS Location,
RPTOBS_3.OBSVALUE AS ProgramSkills, RPTOBS_4.OBSVALUE AS Reason, RPTOBS_5.OBSVALUE AS OverOneHour, PERSON.ADDRESS2
FROM            ML.RPTOBS, ML.RPTOBS RPTOBS_1, ML.RPTOBS RPTOBS_5, ML.RPTOBS RPTOBS_2, ML.RPTOBS RPTOBS_3, ML.PERSON PERSON, ML.RPTOBS RPTOBS_4
WHERE        ML.RPTOBS.PID = RPTOBS_1.PID AND ML.RPTOBS.SDID = RPTOBS_1.SDID AND ML.RPTOBS.PID = RPTOBS_5.PID AND ML.RPTOBS.SDID = RPTOBS_5.SDID AND ML.RPTOBS.PID = RPTOBS_2.PID AND
ML.RPTOBS.SDID = RPTOBS_2.SDID AND ML.RPTOBS.PID = RPTOBS_3.PID AND ML.RPTOBS.SDID = RPTOBS_3.SDID AND ML.RPTOBS.PID = PERSON.PID AND ML.RPTOBS.PID = RPTOBS_4.PID AND
ML.RPTOBS.SDID = RPTOBS_4.SDID AND (PERSON.PSTATUS = 'A') AND (ML.RPTOBS.HDID = 62157) AND (RPTOBS_1.HDID = 62149) AND (RPTOBS_2.HDID = 43266) AND (RPTOBS_5.HDID = 62158) AND
(RPTOBS_3.HDID = 43267) AND (RPTOBS_4.HDID = 62152) AND (PERSON.SEARCHNAME <> 'AATEST, HEALTHY')
ORDER BY PERSON.SEARCHNAME, ML.RPTOBS.OBSDATE

Any help is appreciated!

Posted : May 9, 2019 5:08 am

dmontgomery

(@dmontgomery)

Posts: 59

Trusted Member

Something like this is how I would write that in MSSQL, it looks like you may be using Oracle so you might have to make some changes.

This would require them to have the OBSTERM with HDID = 62157, it would pull in even when they have NULL for 62419. I didn't rewrite your whole query, just enough to give idea. If you need more let me know, I'm no expert in Oracle though.

Select p.PID,
p.LASTNAME,
p.FIRSTNAME,
p.DATEOFBIRTH,
r1.obsdate,
r1.obsvalue,
r2.obsdate,
r2.obsvalue
From RPTOBS r1
INNER JOIN PERSON p ON r1.pid = p.PID
LEFT JOIN RPTOBS r2 ON r1.pid = r2.pid AND r1.sdid = r2.sdid AND r2.hdid = 62149
Where r1.hdid = 62157

Posted : May 10, 2019 9:01 am

gshutter

(@gshutter)

Posts: 5

Active Member

Topic starter

This helped a bunch, I'm getting nulls now in the second rptobs_1 column, but im still having trouble now adding a third rptobs here is my code with just two tables

SELECT        PERSON.SEARCHNAME, PERSON.ADDRESS1, PERSON.CITY, PERSON.PSTATUS, ML.RPTOBS.OBSVALUE AS AdminDate, RPTOBS_1.OBSVALUE AS DischargeDate, PERSON.ADDRESS2
FROM            ML.PERSON PERSON, { oj ML.RPTOBS LEFT OUTER JOIN
ML.RPTOBS RPTOBS_1 ON ML.RPTOBS.SDID = RPTOBS_1.SDID AND RPTOBS_1.HDID = 62149 }
WHERE        PERSON.PID = ML.RPTOBS.PID AND (PERSON.PSTATUS = 'A') AND (ML.RPTOBS.HDID = 62157) AND (PERSON.SEARCHNAME <> 'AATEST, HEALTHY')
ORDER BY PERSON.SEARCHNAME

Posted : May 13, 2019 2:59 am

Share:

24 Forums
9,940 Topics
39.9 K Posts
1 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