How to Query Completed, Unreported, and Unapproved Batches

If yours is a commercial lab you likely work primarily with sample batches in LIMS. You log samples by batch, even for single-sample batches. You report results by batch so that one final report has results for all samples in a single batch. And you generate invoices by sample batch.

When you rely on sample batches in LIMS, you will find it helpful to query samples by batch status. This article shows you how to query completed, completed and unreported, and unapproved batches.

All queries in LIMS query samples. For example, when you perform a query with a batch number criterion, you are asking the LIMS to find all samples with that batch number or all samples in the batch. The result of your query is still a list of samples. When you need to query batches with specific criteria such as completed batches, remember that the results of the query will still be a list of samples in batches matching your criteria. That may be confusing so let’s look at a few examples.

Completed Batches

To query completed samples we only need to enable the Completed option on the Additional tab of the query controls. However, to find completed batches where all samples in the batch are complete requires a subquery or nested query using a Structured Query Language (SQL) expression. A subquery produces a set of data that can be used as part of a query criterion.

Without checking each individual analysis in the sample, we can find a completed sample simply by looking for a non-blank completed date since the LIMS automatically detects when all analyses have a result and adds the completed date. With a subquery we can generate a set of batch numbers for completed batches by finding batches where the number of sample IDs in the batch is equal to the number of non[1]blank completed dates in the batch. We can then find samples within completed batches by finding samples with a batch number in the set of batch numbers produced by the subquery.

To query completed batches first add the following SQL expression to the SQL Expressions screen on the Setup menu.

Name: Completed Batches

SQL: Batch IN (SELECT Batch FROM Sample GROUP BY Batch HAVING Count(SampleID) = Count(CompletedDate))

You can now use the Completed Batches expression as a criterion anywhere you query samples by selecting the expression from the pick list on the SQL tab of the query controls. And you can combine the expression with any other criteria. For example, if you want to find all batches completed for customer ABC Company received this month, select the customer, enter the received date range, and select the Completed Batches SQL expression.

Completed Unreported Batches

If you are using the System Configuration screen option that automatically sets sample reported dates, you can extend our previous example and find completed and unreported batches with two subqueries. Just as in our previous example, the first subquery generates a set of batch numbers for completed batches. The second subquery generates a set of batch numbers where the number of samples in the batch is different than the number of non-blank reported dates in the batch to reveal unreported batches. The intersection of the sets resulting from these two subqueries is a list of batches that are both completed and unreported. To query samples in completed unreported batches add the following expression to the SQL Expressions screen then select the expression on the SQL tab of the query controls.

Name: Completed Unreported Batches

SQL: Batch IN (SELECT Batch FROM Sample GROUP BY Batch HAVING Count(SampleID) = Count(CompletedDate)) AND Batch IN (SELECT Batch FROM Sample GROUP BY Batch HAVING Count(SampleID) <> Count(ReportedDate))

Unapproved Batches If you are using sample approval in LIMS, you can find unapproved samples by simply selecting the Unapproved option on the Additional tab of the query controls. In an unapproved sample, the only incomplete analysis is the defined “Approval” analyte. Therefore, an unapproved batch is a batch where all samples in the batch are unapproved.

Multiple subqueries are also required to find unapproved batches. The first subquery finds samples having only one incomplete analysis. The second subquery finds samples with an incomplete “Approval” analyte. The intersection of the sets produced by these two queries yields a list of unapproved samples. If the count of unapproved samples in a batch is equal to the total number of samples in the batch then the batch is unapproved. To query samples in unapproved batches add the following expression to the SQL Expressions screen then select the expression on the SQL tab of the query controls.

Name: Unapproved Batches

SQL: Batch IN (SELECT Batch FROM Sample WHERE Batch IS NOT NULL AND ((Sample.SampleID IN (SELECT SampleID FROM SampleAnalysis WHERE AnalysisResult IS NULL AND ResultTypeID IS NULL GROUP BY SampleID HAVING Count(AnalysisID) = 1)) AND (Sample.SampleID IN (SELECT SampleID FROM SampleAnalysis WHERE AnalysisID=999 AND AnalysisResult IS NULL AND ResultTypeID IS NULL))) GROUP BY Batch HAVING Count(SampleID) = (SELECT Count(SampleID) FROM Sample AS S2 WHERE S2.Batch = Sample.Batch))

Replace 999 in the expression above with the internal AnalysisID for your “Approval” analyte. To find the AnalysisID open any sample query dialog and select the Unapproved check box on the Additional tab, click Query, then select the SQL tab and find ‘WHERE AnalysisID=n’ in the ‘SQL for last query’ field. Replace 999 with n.

Like any sample query, a query using one of the above SQL expressions produces a list of samples. You can get a list of just the batch numbers by exporting to Excel. Export a Sample Summary report of the queried samples to the generic LIMS Export Template, delete all but the Batch column then use the Remove Duplicates option on the Data tab.