Notifications
Clear all
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
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
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
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
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
Topic starter
That worked. Thank you so much!
Posted : January 24, 2019 4:58 am
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
I am not experiencing the same issue, you are calculating the sum of the count(*), correct?
Posted : January 24, 2019 5:24 am
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
