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 Database Query for Identifying Documents

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

9 Posts

2 Users

0 Reactions

70 Views

RSS

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

I am looking for a way to pull a list of all chart documents that contain "Cerner" in the document text, for a given date range, with report results that include the document summary. The Document text and Document summary use different data tables.

What we are trying to do is identify and analyze the types of documents currently being received by one of our hospital LinkLogic interfaces. They recently migrated to a different EMR and some of their documents stopped flowing. They are asking us to identify which types are flowing and which types are not. The best way I can think of doing this on a large scale is to pull up a report of all they are currently exporting to us, using the unique identifier "Cerner" in the text, and then sorting the results by the summary line, which identifies the type of report.

Would anyone have a SQL query that can do this? It's the link or match function between the two data tables is what I'm having trouble with.

Thank you.

Posted : January 24, 2019 1:40 am

jtlewis

(@jtlewis)

Posts: 35

Eminent Member

The below query should do the trick. This will allow you to pull in any documents containing Cerner within the date range, then also count how much of each summary. Please keep in mind this is based off of DB_CREATE_DATE and not necessarily the Clinical Date of that document.

select d.SUMMARY as DocumentSummary, count(*) from DOCUMENT d
inner join DOCDATA dd on d.SDID = dd.SDID
where dd.DATA like '%Cerner%' and cast(d.DB_CREATE_DATE as date) >= '1.22.19' and cast(d.DB_CREATE_DATE as date) < '1.24.19'
group by D.SUMMARY

Posted : January 24, 2019 4:31 am

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

This works perfectly and extremely helpful. Thank you! The additional count column will help identify the volume.

Would it be possible to also include the document type? That must be a separate field in the table, even though it appears together in the chart note with the summary itself.

Posted : January 24, 2019 4:40 am

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

Just noticed the biggest volume since a June 1st date I entered ( 2201) has a blank summary line text , so I think the document type does need to be included, for those where no manual text was added to the summary line.

Thank you for your help with this. The goal for us is to compare and identify the missing report types and overall improve patient care by ensuring all reports are being received for our patient hospital visits.

Posted : January 24, 2019 4:46 am

jtlewis

(@jtlewis)

Posts: 35

Eminent Member

Definitely can, just need to join the Doctypes table in.

select dt.DESCRIPTION as DocType, d.SUMMARY as DocumentSummary, count(*) from DOCUMENT d
inner join DOCDATA dd on d.SDID = dd.SDID
inner join DOCTYPES dt on d.DOCTYPE = dt.DTID
where dd.DATA like '%Cerner%' and cast(d.DB_CREATE_DATE as date) >= '1.22.19' and cast(d.DB_CREATE_DATE as date) < '1.24.19'
group by dt.DESCRIPTION, d.SUMMARY

Posted : January 24, 2019 4:47 am

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

That worked. Thank you so much!

Posted : January 24, 2019 4:58 am

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

Little glitch..

The first query without the document type column resulted in 1188 rows, with volume sum = 9331 documents.

The second query with the document type column resulted in 39 rows, with volume sum = 102 documents.

Posted : January 24, 2019 5:12 am

jtlewis

(@jtlewis)

Posts: 35

Eminent Member

I am not experiencing the same issue, you are calculating the sum of the count(*), correct?

Posted : January 24, 2019 5:24 am

[email protected]

(@anna-rodriguedfdrussell-org)

Posts: 141

Estimable Member

Topic starter

My mistake, apologies. I didn't update the start date. Re-ran it and it's perfect.

Thank you

Posted : January 24, 2019 5:39 am

Share:

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