SQL for Unique Queries

In any lab, there is a lot of useful information lying within the LIMS database. For example, one commercial lab wanted to glean some important information about their customers from LimsData. For a given date range they wanted a count of either the number of samples or the number of batches logged. While an invoice report could be used to get the sample counts, the report can not provide a batch count and they wanted a more concise listing.

Appropriate Structured Query Language (SQL) statements and the LIMS Data Query workbook available in the File Library provided a simple solution. Adding the following SQL statement to a copy of the LIMS Data Query workbook produced a list of customers with the total number of batches logged during a given date range.

SELECT Customer.Name, Count(Batch) AS BatchesLogged FROM (SELECT DISTINCT Customer.Name, Batch FROM Customer LEFT JOIN Sample ON Customer.CustomerID = Sample.CustomerID WHERE (Sample.AddedDate BETWEEN #1/1/2005 00:00:00# AND #12/31/2005 23:59:59#)) GROUP BY Customer.Name HAVING Count(Batch) > 0;

The LIMS Data Query workbook was also the solution in another request to extract specific data from the LIMS database. A user with a large number of projects needed to find all project analyses where at least one specification (i.e. results entry or warning minimum or maximum) differed from the analysis’ default specifications. While the Projects report will list all project analysis specifications, it would be a daunting task to manually compare each value to those on the Analyses report.

The following SQL statement added to a copy of the LIMS Data Query workbook produced a sorted list of analyses with their default specifications and the project name and project analysis specifications where at least one specification differed.

SELECT Analysis.Analyte, Analysis.ResultMax, Analysis.WarningMax, Analysis.WarningMin, Analysis.ResultMin, Project.Name AS Project, ProjectAnalysis.ResultMax, ProjectAnalysis.WarningMax, ProjectAnalysis.WarningMin, ProjectAnalysis.ResultMin FROM Analysis INNER JOIN (Project INNER JOIN ProjectAnalysis ON Project.ProjectID = ProjectAnalysis.ProjectID) ON Analysis.AnalysisID = ProjectAnalysis.AnalysisID WHERE (((Analysis.ResultMax)<>[ProjectAnalysis] .[ResultMax])) OR (((Analysis.WarningMax)<>[ProjectAnalysis ].[WarningMax])) OR (((Analysis.WarningMin)<>[ProjectAnalysis ].[WarningMin])) OR (((Analysis.ResultMin)<>[ProjectAnalysis] .[ResultMin])) ORDER BY Analysis.Analyte, Project.Name;

When you cannot retrieve the necessary data using an existing LIMS report, a more general-purpose tool such as the LIMS Data Query workbook is a perfect solution. Keep this valuable tool in mind when you need to extract information from the LIMS database.