Hi Jimmy,
Some of the items in your query would vary based on your solution. For instance, we'd need the Keyword Type Numbers for each of the Keywords you intend to utilize. I'll provide you with a basic shell of the statement, but if you want a more complete statement, I'd recommend calling your first line of support. You may also find our Database Reporting Guide helpful since it will provide you with how these tables relate and what the various values indicate.
For the shell of the query I'm providing, you'll need to fill in the Keyword Type Numbers for the XXX in each hsi.keyitemXXX. I also wasn't sure what the Upload portion was supposed to be, so you may need to add an additional Keyword Type if that's what it is:
select ki1.keyvaluechar as 'Last Name', ki2.keyvaluechar as 'First Name', ki3.keyvaluechar as 'MRN', ki4.keyvaluechar as 'FIN', dt.itemtypename as 'Doc Type', hel.logdate as 'HL7 Send Time', 'Status' = CASE
when hel.status = 0 then 'Unsent'
when hel.status = 1 then 'Failure'
when hel.status = 2 then 'Resent/Failure'
when hel.status = 3 then 'Resent/Success'
when hel.status = 4 then 'Success'
when hel.status = 6 then 'Message could not be Constructed'
when hel.status = 1073741824 then 'Purged'
END
from hsi.hl7eventlog hel
join hsi.itemdata id on hel.itemnum = id.itemnum
join hsi.doctype dt on id.itemtypenum = dt.itemtypenum
join hsi.keyitemXXX ki1 on id.itemnum = ki1.itemnum
join hsi.keyitemXXX ki2 on id.itemnum = ki2.itemnum
join hsi.keyitemXXX ki3 on id.itemnum = ki3.itemnum
join hsi.keyitemXXX ki4 on id.itemnum = ki4.itemnum
This query should get you started, but as I said, your first line of support should be able to assist you with getting it to fit your solution. Thank you!
Matt Butler
Technical Support Analyst, Healthcare Support
Hyland Software, Inc.
