Audit Report Sample SQL

gistfile1.sql

-- For each user run this query:
Select d.DocId, DocTypeName, OwnerTypeName, CreateDateTime,
       EncounterNo, MedRecNo, EnrolleeName
From Documents as d
Inner Join DocTypes as dt on
d.DocType = dt.DocType
Inner Join DocsOwners as do on
d.DocId = do.DocId
Inner Join Owners as o on
do.OwnerId = o.OwnerId
Inner Join OwnerTypes as ot on
o.OwnerType = ot.OwnerType
--------------------------------------------|
-- Get the index value for the Owner Types -|
--------------------------------------------|
Left Outer Join Encounters as enc on
do.OwnerId = enc.EncntrOwnerId
Left Outer Join MedicalRecords as mr on
do.OwnerId = mr.MedRecOwnerId
Left Outer Join Enrollees as enr on
do.OwnerId = enr.EnrolleeOwnerId
Where CreateUser = 'r0kw_rihreg'
AND CreateDateTime >= '05/04/2009 12:00:00 AM' 
AND CreateDateTime <= '05/04/2009 11:59:59 PM'
-------------------------------------------------|
-- Replace the dates dynamically in the script. -|
-- Replace the user dynamically in the script.  -|
-------------------------------------------------|