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

Crystal Report Document Content

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

5 Posts

4 Users

0 Reactions

52 Views

RSS

jkratzer

(@jkratzer)

Posts: 138

Estimable Member

Topic starter

Does anyone have a crystal report (or just and SQL query) that searches the content of a document for a specified string?

Posted : April 22, 2016 5:40 am

gpincomb

(@gpincomb)

Posts: 278

Reputable Member

Do you mean something like:

select*

from DOCDATA dd

where dd.DATA like'%cessation%'

Of course, you'll probably want to link to the Document table via SDID and then from that to the PatientProfile table via PID.

Posted : April 22, 2016 5:53 am

jfitzmd

(@jfitzmd)

Posts: 419

Reputable Member

I have a Crystal Report to extract a string from a document, filtered by document date, document type. If you are interested in the report, contact me at [email protected]. The SQL is as follows:

SELECT "PatientProfile"."Last", "PatientProfile"."First", "PatientProfile"."PatientId", "DOCUMENT"."CLINICALDATE", "DOCDATA"."DATA", "DOCTYPES"."ABBR"
FROM (("CentricityPM"."dbo"."DOCDATA" "DOCDATA" INNER JOIN "CentricityPM"."dbo"."DOCUMENT" "DOCUMENT" ON "DOCDATA"."SDID"="DOCUMENT"."SDID") INNER JOIN "CentricityPM"."dbo"."PatientProfile" "PatientProfile" ON "DOCUMENT"."PID"="PatientProfile"."PId") INNER JOIN "CentricityPM"."dbo"."DOCTYPES" "DOCTYPES" ON "DOCUMENT"."DOCTYPE"="DOCTYPES"."DTID"
WHERE "DOCTYPES"."ABBR"='Echo'

Posted : April 22, 2016 6:50 am

lkeith

(@lkeith)

Posts: 457

Honorable Member

Depending on what you are wanting to do with the report, I have done Inquiries in Chart reports using:

Find: Documents

Where: Document Text

Filter: Is (or) Contains

Filter: string of text you need

Posted : April 22, 2016 7:18 am

jkratzer

(@jkratzer)

Posts: 138

Estimable Member

Topic starter

Since I needed both the signed and unsigned in the report, I had to query both the DOCDATA and the DOCUMENT data.

Here is the query I finally came up with:

SELECT PatientProfile.PatientId,PatientProfile.First,PatientProfile.Middle,PatientProfile.Last,USR.LOGINNAME, DOCUMENT.STATUS,convert(VARCHAR(40),dbo.Convert_ID_to_date(DOCUMENT.CLINICALDATE),100)as VISITDATE

FROM (USR USR INNERJOIN(PatientProfile PatientProfile INNERJOIN DOCUMENT DOCUMENT ON PatientProfile.PId=DOCUMENT.PID)ON USR.PVID=DOCUMENT.USRID)

INNERJOIN DOCDATA DOCDATA ON DOCUMENT.SDID=DOCDATA.SDID

WHERE convert(VARCHAR(10),dbo.Convert_ID_to_date(DOCUMENT.CLINICALDATE),110)=convert(VARCHAR(10),GETDATE(),110)

and DOCUMENT.DOCTYPE in(1,1421060190000950,1421060190000950,1426091203000880,1426091233000880,1436789521001310)

and DOCDATA.DATA LIKE '%Your Text%'

UNIONALL

SELECT PatientProfile.PatientId, PatientProfile.First,PatientProfile.Middle,PatientProfile.Last,USR.LOGINNAME, DOCUMENT.STATUS,convert(VARCHAR(40),dbo.Convert_ID_to_date(DOCUMENT.CLINICALDATE),100)as VISITDATE

FROM (USR USR INNERJOIN(PatientProfile PatientProfile

INNERJOIN DOCUMENT DOCUMENT ON PatientProfile.PId=DOCUMENT.PID)ON USR.PVID=DOCUMENT.USRID)

WHERE convert(VARCHAR(10),dbo.Convert_ID_to_date(DOCUMENT.CLINICALDATE),110)=convert(VARCHAR(10),GETDATE(),110)

and DOCUMENT.DOCTYPE in(1,1421060190000950,1421060190000950,1426091203000880,1426091233000880,1436789521001310)

and DOCUMENT.HOLDTEXT LIKE '%Your Text%'

Thanks to everyone for pointing me in the right direction.

Posted : April 26, 2016 4:15 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