Recently I needed a way to essentially ‘loop’ through all the records in our NetTrax.NET Audit History for the given record and return back the one that is that entries ‘check in’…
So, what I did is I create a view that UNIONs all the Audit tables together (FoldersAudit, FoldersAudit_EmpComp, FoldersAudit_HR, DocumentsAudit, DocumentsAudit_EmpComp, and DocumentsAudit_HR)
SELECT * FROM tbl_FoldersAudit UNION ALL SELECT * FROM tbl_FoldersAudit_EmpComp UNION ALL SELECT * FROM tbl_FoldersAudit_HR UNION ALL SELECT * FROM tbl_DocumentsAudit UNION ALL SELECT * FROM tbl_DocumentsAudit_EmpComp UNION ALL SELECT * FROM tbl_DocumentsAudit_HR
That guy returns almost 3 million records so I wouldn’t suggest running it as
SELECT * FROM…
So, now that I have ALL my audit entries into one nice view, then I wrote my function… I pass in the rows Auditing_ID and Audit_TS then I evaluate the top 1 record where the date is greater than the Auditing_TS I passed in. There is some other logic as far as checkin / checkout type but you’ll see that below in the actual function
[dbo].[fn_CalculateCheckInDate] ( -- Add the parameters for the function here @AuditingID VARCHAR(38), @AuditTS DATETIME ) RETURNS DATETIME AS BEGIN DECLARE @Return AS DATETIME --Go get the first record from the AuditUnion view where the Audit_TS is greater than --the Audit_TS of the record passed in and the AuditMetricID is a 'check in' or 'check out'. --The next (first) record after this one by date would be the next transfer. SET @Return = ( SELECT TOP 1 Audit_TS FROM vw_AuditUnion WHERE Audit_TS > @AuditTS AND Auditing_ID = @AuditingID AND AuditMetricID IN (8,9) ORDER BY Audit_TS ) Return @Return END
Now, I can call the function as a column in the select view (for my report) and get the timestamp of the check in. I’m passing in a hardcoded Auditing_ID for testing purposes right now but obviously this will be different criteria when I run the report (it will be a date range). I’m saying WHERE AuditMetricID IN (9) because in the report I only want rows for check out’s nothing else.
SELECT Audit_TS, dbo.fn_CalculateCheckInDate(Auditing_ID, Audit_TS) AS 'CheckIn_TS' FROM tbl_FoldersAudit FA WHERE Auditing_ID = '83ba4bae-9a8d-4bd9-958f-81355ad80fe7' AND AuditMetricID IN (9) ORDER BY Audit_TS
Here is a side-by-side of the NetTrax Audit History and my SQL Results
You’ll notice that my “WhoCares” line isn’t included because in my select, and in my function I’m only looking at Check In / Check Out so I won’t erroneously get the audit row for an edit or a mass update for example!