New post
Question
· Feb 6, 2017

Determine which repository a document came from via SQL

#Caché #HealthShare #SQL

I have the following query which tells me how many documents were retrieved for each customer, but it only works for the "on-demand" customers:

SELECT PatientFacility, LEFT(LocalDateTime,7) as Mnth, Count(*)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType IN ('RecordRequest','RecordRequestBreakGlass')
AND LocalDateTime >= '2016-01-01'
AND LocalDateTime < '2017-01-01'
GROUP BY PatientFacility, LEFT(LocalDateTime,7)

 

All the other PatientFacility's show up as "Documents".  In an effort to break those down too, I first parsed the DocumentUniqueId value from the XML contained in the Criteria column.  I then did a JOIN on the HS_Registry.document table to get the SourceIdentifier_Value which is an OID, and subsequently the IdentityCode from the HS_Data.OIDMap table.  Unfortunately... they all show up as MHCNETWORK (our internal AA).  Here is the full query:

SELECT TOP 5000 LocalDateTime, PatientFacility, $PIECE($PIECE(criteria,'<AdditionalInfoItem AdditionalInfoKey="DocumentUniqueId">',2),'</AdditionalInfoItem>',1) as DocId, d.SourceIdentifier_Value, oid.IdentityCode, EnsembleSessionId, UserName
FROM HS_IHE_ATNA_Repository.Aggregation a
JOIN HS_Registry.document d
    ON $PIECE($PIECE(a.criteria,'<AdditionalInfoItem AdditionalInfoKey="DocumentUniqueId">',2),'</AdditionalInfoItem>',1) = d.DocumentId
JOIN HS_Data.OIDMap oid
    ON d.SourceIdentifier_Value = oid.OID
WHERE A.EventType IN ('RecordRequest','RecordRequestBreakGlass')
order by A.ID desc

 

Where did I go wrong?

Discussion (4)0
Log in or sign up to continue
Saul Bloom · Feb 7, 2017

The "documents" that you are finding with your query are not documents, but are SDA record requests.  If you are looking for retrievals of IHE documents from a document repository, the event type to use is "Retrieve Document Set".  You didn't mention what version of HealthShare you are running, but in recent versions, the ATNA repository has a relationship to a child table, HS_IHE_ATNA_Repository.Document, and you can join to that.  It has a RepositoryID and DocumentID property.

1 0
Scott Beeson · Feb 7, 2017

Thanks for the response!

For the version: Cache for UNIX (SUSE Linux Enterprise Server for x86-64) 2015.1.1 (Build 505_1_15646U) Tue Sep 1 2015 13:14:21 EDT [HealthShare Modules:Core:14.0.7403 + Linkage Engine:13.05.7403 + Patient Index:13.04.7403 + Clinical Viewer:14.0.7403 + Active Analytics:14.0.7403]

 

I am looking at the HS_IHE_ATNA_Repository.Document table now, thanks!

0 0