Sample Turnaroun

Q: “Attached are the versions of the UDR set up and the output. … I would also like an average turn around. Would I copy the formula from the MSC LIMS insight Jan 07 page 4, bottom right into the ‘text’ section for “headers and footers”?”

Ideally, I would like a concise report detailing the turnaround time on all of our projects. A simple one[1]page document with the samples logged in and completed in the month.

A: Using the difference between a sample’s login date (i.e. AddedDate field) and completed date, you can show a sample’s turnaround time in hours on a UDR with this column expression:

=DateDiff(“h”, [AddedDate], [CompletedDate])

To show the turnaround time in days, use this expression:

=DateDiff(“d”, [AddedDate], [CompletedDate])

Notice that the only difference in these formulas is the interval: “h” for hours, and “d” for days.

Finally, you can show the average turnaround in both hours and days in the report’s Text field, which displays on the last page of the generated report, with this expression:

=”Average Turnaround: ” & Round(Avg(DateDiff(“h”, [AddedDate], [CompletedDate])),0) & ” Hours or ” & Round(Avg(DateDiff(“d”, [AddedDate], [CompletedDate])),0) & ” Days.”

The expression above rounds both hours and days to no decimal places, which is the “,0)”. Change these to “,1)” to round to one decimal place, etc.

For a concise summary of average sample turnaround in days by LIMS project, a simple SQL SELECT statement will do the job. Add the following SELECT statement to an LIMS Data Query workbook:

SELECT Project.Name AS Project, Count(Sample.SampleID) AS Samples, Round(Avg(DateDiff(“d”, [Sample.AddedDate], [Sample.CompletedDate])),2) AS AvgTurnaroundDays FROM Project INNER JOIN Sample ON Project.ProjectID = Sample.ProjectID WHERE Sample.CompletedDate Is Not Null AND Sample.AddedDate Between #12/1/2019 00:00:00# And #12/31/2019 23:59:59# GROUP BY Project.Name;

Simply edit the login date range in the SQL statement and click the Query button while LIMS is open to retrieve the data. Turnaround is calculated as the time between login and completion so only completed samples are counted. See “Updated LIMS Data Query Workbook Now Available” in LIMS Insights Issue 23 for more information on this valuable tool. And see the workbook’s ReadMe worksheet for other example SQL statements you may find useful