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)
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
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?