Matrix Gemini LIMS by Autoscribe Informatics (www.autoscribeinformatics.com/lims)

Uses the SAMPLE and Custom_SAMPLERESULTS Tables (linked on SAMPLECODE).

NOTE:  The Date fields in the tables are integers in the form of YYYYMMDD and the Time fields are the number of minutes since midnight.  Therefore to calculate a time you need the following syntax:

DATEADD(mi,SampleTime1,CAST(SUBSTRING(CAST(SampleDate1 AS VARCHAR),5,2) + '/' + RIGHT(CAST(SampleDate1 AS VARCHAR),2) + '/' + LEFT(CAST(SampleDate1 AS VARCHAR),4) AS DATETIME)) AS SAMPDATE

EXAMPLE 1: Directly query the Samples and Custom_SAMPLERESULTS Tables.

SELECT * FROM (Select SAMPLES.SampleCode AS SAMPLENUM, Units,ComponentCode AS ANALYTE,ReportedResult AS FINAL, SampleText2 as SAMPLOC, DATEADD(mi,SampleTime1,CAST(SUBSTRING(CAST(SampleDate1 AS VARCHAR),5,2) + '/' + RIGHT(CAST(SampleDate1 AS VARCHAR),2) + '/' + LEFT(CAST(SampleDate1 AS VARCHAR),4)  AS DATETIME)) AS SAMPDATE, DATEADD(mi,ModifyTime,CAST(SUBSTRING(CAST(ModifyDate AS VARCHAR),5,2) + '/' + RIGHT(CAST(ModifyDate AS VARCHAR),2) + '/' + LEFT(CAST(ModifyDate AS VARCHAR),4)  AS DATETIME)) AS MODIFY_DATE
from Samples,Custom_SAMPLERESULTS Where Samples.SAMPLECODE =Custom_SAMPLERESULTS.SAMPLECODE AND SAMPLES.AuditFlag=0 AND RECORDSTATUS=1 AND ReportedResult IS NOT NULL AND SAMPLETEXT15 IS NULL) AS S
WHERE MODIFY_DATE>'2013-06-20' AND Modify_DATE < '2013-06-26'

NOTE: SampleText15 IS NULL removes QC samples.

EXAMPLE #2: Create a View

The following is a second example of a query that was used.  This paticular query was used to create a view and the Interface used the view (view_WIMS)

SELECT SAMPNUM, Units, ANALYTE, ReportedResult, SAMPLOC, SAMPDATE, SampleDate1, MODIFYDATE
FROM (SELECT dbo.Samples.SampleCode AS SAMPNUM, dbo.Custom_SampleResults.Units, dbo.Custom_SampleResults.ComponentCode AS ANALYTE,
dbo.Custom_SampleResults.ReportedResult, dbo.Samples.SampleText2 AS SAMPLOC,
DATEADD(mi, dbo.Samples.SampleTime1, CAST(SUBSTRING(CAST(dbo.Samples.SampleDate1 AS VARCHAR), 5, 2) + '/' + RIGHT(CAST(dbo.Samples.SampleDate1 AS VARCHAR), 2) + '/' + LEFT(CAST(dbo.Samples.SampleDate1 AS VARCHAR), 4) AS DATETIME)) AS SAMPDATE,
DATEADD(mi,dbo.Custom_SampleResults.ModifyTime, CAST(SUBSTRING(CAST(dbo.Custom_SampleResults.ModifyDate AS VARCHAR), 5, 2)
+ '/' + RIGHT(CAST(dbo.Custom_SampleResults.ModifyDate AS VARCHAR), 2) + '/' + LEFT(CAST(dbo.Custom_SampleResults.ModifyDate AS VARCHAR), 4) AS DATETIME)) AS MODIFYDATE
FROM dbo.SampleNotes RIGHT OUTER JOIN dbo.Custom_SampleResultNotes RIGHT OUTER JOIN dbo.Custom_SampleResults INNER JOIN dbo.Samples INNER JOIN dbo.SampleTests ON dbo.Samples.SampleCode = dbo.SampleTests.SampleCode AND dbo.Samples.AuditFlag = dbo.SampleTests.AuditFlag ON dbo.Custom_SampleResults.TestPosition = dbo.SampleTests.TestPosition AND dbo.Custom_SampleResults.SampleCode = dbo.SampleTests.SampleCode AND dbo.Custom_SampleResults.TestCode = dbo.SampleTests.TestCode ON dbo.Custom_SampleResultNotes.SampleCode = dbo.Custom_SampleResults.SampleCode AND dbo.Custom_SampleResultNotes.RecordStatus = dbo.Custom_SampleResults.RecordStatus ON dbo.SampleNotes.SampleCode = dbo.Samples.SampleCode AND dbo.SampleNotes.AuditFlag = dbo.Samples.AuditFlagWHERE (dbo.Samples.SampleText14 IS NULL) AND (dbo.Samples.AuditFlag = 0) AND (dbo.Custom_SampleResults.RecordStatus = 1) AND (dbo.SampleTests.TestStatus = 40) AND (dbo.Samples.SampleDate1 IS NOT NULL) AND (LEN(dbo.Samples.SampleDate1) = 8) AND (LEN(dbo.Custom_SampleResults.ModifyDate) = 8)) AS S

EXAMPLE 3: View with Dates as integers

A view was setup (View_WIMSData) with the SampDate and ValidateDate brought in as integers:

Select SampLoc, Analyte, Final, ComponentValue, Units,

left(cast(SampDate as varchar),4)+'-'+substring(cast(SampDate as varchar),5,2)+'-'+SUBSTRING(cast(SampDate as varchar),7,2) as SampDATE

from View_WIMSData

where cast(left(cast(validateDate as varchar),4)+'-'+substring(cast(ValidateDate as varchar),5,2)+'-'+SUBSTRING(cast(validateDate as varchar),7,2) as DATETIME) >= #SD#

and cast(left(cast(validateDate as varchar),4)+'-'+substring(cast(ValidateDate as varchar),5,2)+'-'+SUBSTRING(cast(validateDate as varchar),7,2) as DATETIME) <= #ED#

EXAMPLE 4: View with the ability to import A and P for Total Coliform and E.coli

SELECT        dbo.Samples.SampleCode AS Lab#, dbo.Samples.SubmitterCode AS Client, dbo.Samples.SampleText10 AS SampLoc, dbo.Samples.SampleText2 AS Project, REPLACE(dbo.SampleResults.ComponentName,

                         ' Reported Result', '') AS Analyte, dbo.SampleResults.EnteredValue AS Final, dbo.SampleResults.ComponentValue, ISNULL(dbo.SampleResults.ComponentUnits, '') AS Units,

                         dbo.Samples.SampleDate2 AS SampDate, dbo.Samples.SampleTime2 AS SampTime, dbo.SampleResults.EnterDate AS [Modified Date], dbo.SampleTests.ValidateDate

FROM            dbo.Samples INNER JOIN

                         dbo.SampleTests ON dbo.Samples.SampleCode = dbo.SampleTests.SampleCode AND dbo.Samples.AuditFlag = dbo.SampleTests.AuditFlag INNER JOIN

                         dbo.SampleResults ON dbo.SampleTests.SampleCode = dbo.SampleResults.SampleCode AND dbo.SampleTests.AuditFlag = dbo.SampleResults.AuditFlag AND

                         dbo.SampleTests.TestPosition = dbo.SampleResults.TestPosition

WHERE        (dbo.Samples.AuditFlag = 0) AND (dbo.SampleTests.TestStatus = 40) AND (dbo.Samples.SampleStatus <> 900) AND (dbo.SampleResults.ResultStatus <> 0) AND (dbo.SampleResults.ComponentRow > 0) AND

                         (dbo.SampleResults.ComponentColumn IN (7, 13))

QUERY to use in Source Configuration