Scott Beeson · Feb 6, 2017

Determine which repository a document came from via 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?

2 0 4 280
Log in or sign up to continue


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.

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!

Does this mean I'll need to do a separate query for stored documents and on demand documents (Retrieve Document Set vs Record Request)?

Also, while looking at the list of Retrieve Document Set events, I noticed none of the MPI or MRN fields have data.  Is that expected?