Notifications
Clear all
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
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
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
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
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
