---
title: "Dashboard QAQC Standard and Repeats"
canonical: "https://servicedesk.maxgeo.com/space/DS/1422000153/Dashboard%20QAQC%20Standard%20and%20Repeats"
format: markdown
---
| **Chart/Table name** | **Datasource Name** | **Views/tables ** |
| --- | --- | --- |
| Filters: All Batches | All Batches Source | ```sql
SELECT a.Batch_No AS Batch_No FROM(SELECT DISTINCT dataset, Batch_No FROM tblAssay WHERE labcode IN(@filtLab)AND dataset IN(@filtDataset)) a
INNER JOIN tblSYSAssBatch b ON a.Batch_No = b.Batch_No
WHERE Lab_Final_Date BETWEEN @filtDateFROM AND @filtDateTo AND Batch_Status IN(@filtBatchStatus)
ORDER BY Batch_No
``` |
| Filters:
Standard Filter | Standard Filter Source | ```sql
SELECT DISTINCT TBLSTANDARDSAMP.StandardID FROM TBLSTANDARDSAMP
INNER JOIN TBLASSAY ON(TBLSTANDARDSAMP.SampleID = TBLASSAY.SampleID) AND (TBLSTANDARDSAMP.DataSet = TBLASSAY.DataSet)
WHERE TBLASSAY.Batch_No IN(@filtBatchNo)
AND TBLASSAY.DataSet in(@filtDataset)
AND TBLSTANDARDSAMP.StANDardID IS NOT NULL
ORDER BY TBLSTANDARDSAMP.StANDardID;
``` |
| Filters: Standard Method Filter | Standard Method Filter Source | ```sql
SELECT DISTINCT TBLASSAY.GenericMethod AS GENMETH FROM TBLASSAY
INNER JOIN TBLSTANDARDSAMP ON(TBLASSAY.SampleID = TBLSTANDARDSAMP.SampleID) AND (TBLASSAY.DataSet = TBLSTANDARDSAMP.DataSet)
WHERE TBLASSAY.Batch_No IN(@filtBatchNo)
AND TBLSTANDARDSAMP.StANDardID IN(@Filter_QAQC_StANDards)
AND tblStANDardSamp.DataSet IN (@filtDataset)
AND TBLASSAY.GenericMethod IS NOT NULL;
``` |
| Filters:
Standard Element Filter | Standard Element Filter Source | ```sql
SELECT DISTINCT TBLASSAY.Element, E.SystemUnits FROM TBLASSAY
INNER JOIN TBLSTANDARDSAMP ON(TBLASSAY.SampleID = TBLSTANDARDSAMP.SampleID)
AND (TBLASSAY.DataSet = TBLSTANDARDSAMP.DataSet)
INNER JOIN tblSYSAssElement E ON E.Element = tblAssay.Element
WHERE TBLASSAY.Batch_No IN(@filtBatchNo)
AND TBLASSAY.Element IS NOT NULL
AND TBLSTANDARDSAMP.StANDardID IN (@Filter_QAQC_StANDards)
AND tblAssay.GenericMethod in (@genericMethod)
AND tblAssay.DataSet in (@filtDataset)
ORDER BY TBLASSAY.Element;
``` |
| Chart: Multi Standard | Multi Standard Source | ```sql
SELECT ID = DENSE_RANK() OVER(ORDER BY RID) ,* FROM (SELECT tblAssay.SampleID,TBLASSAY.sysResult,tblSYSAssElement.SystemUnitsTBLSTANDARDSAMP.StANDardID,tblSYSAssBatch.Lab_job_date ,RID = DENSE_RANK() OVER(ORDER BY Lab_Job_Date,TBLASSAY.Batch_No,TBLASSAY.SampleID)FROM tblSYSAssBatch
INNER JOIN((TBLASSAY INNER JOIN TBLSTANDARDSAMP ON(TBLASSAY.DataSet = TBLSTANDARDSAMP.DataSet AND (TBLASSAY.SampleID = TBLSTANDARDSAMP.SampleID))
INNER JOIN tblSYSAssElement ON TBLASSAY.Element = tblSYSAssElement.Element) ON(tblSYSAssBatch.batch_no = TBLASSAY.Batch_No) AND (tblSYSAssBatch.LabCode = TBLASSAY.LabCode) WHERE(TBLSTANDARDSAMP.StANDardID IN (@Filter_QAQC_StANDards))
AND (TBLASSAY.Element = @Filter_QAQC_Element)
AND TBLASSAY.Repeat = '0' AND TBLASSAY.Batch_No IN (@filtBatchNo)
AND ((TBLASSAY.Preferred = 0 AND tblSYSAssBatch.batch_status in ('A','QC_NA'))
OR (TBLASSAY.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND tblSYSAssBatch.batch_status in ('R','Q')))
AND TBLASSAY.sysResult IS NOT NULL
AND tblAssay.GenericMethod IN (@genericMethod)
AND tblAssay.DataSet IN (@filtDataset) ) AS t
PIVOT(MAX(sysResult) FOR StANDardID IN (@filtQAQCStANDard)) AS p
``` |
| Chart: Multi Standard Expected Value | Multi Standard Expected Value Source | ```sql
SELECT ExpectedValue = ExpectedValue * ISNULL(C.ConversionFactOR,1),ExpectedMin = ExpectedMin * ISNULL(C.ConversionFactOR,1), ExpectedMax = ExpectedMax * ISNULL(C.ConversionFactOR,1),
ExpectedStDev = ExpectedStDev * ISNULL(C.ConversionFactOR,1), C.CodeTo AS Units,GenericMethod, ExpectedOutlier = ExpectedOutlier * ISNULL(C.ConversionFactOR,1), Preferred,
StANDardID FROM ( SELECT * ,RID = ROW_NUMBER() OVER(PARTITION BY StANDardID ORDER BY Preferred)
FROM ( SELECT ExpectedValue, ExpectedMin,
ExpectedMax, ExpectedStDev, Units, GenericMethod,
ExpectedOutlier, -1 AS preferred, StANDardID, Element
FROM tblSYSQCReferenceValue
WHERE StANDardID in (@Filter_QAQC_StANDards)
AND Element = @Filter_QAQC_Element
AND GenericMethod in (@genericMethod)
union
SELECT ExpectedValue,
ExpectedMin,ExpectedMax,ExpectedStDev,Units,
GenericMethod, ExpectedOutlier, Preferred,
StANDardID , Element
FROM tblSYSQCReferenceValue WHERE StANDardID in (@Filter_QAQC_StANDards)AND Element = @Filter_QAQC_Element ) a ) r
INNER JOIN tblSYSAssElement E ON R.Element = E.Element
LEFT JOIN tblSYSConversionFactOR C ON C.CodeFROM = R.Units AND C.CodeTo = E.SystemUnits
WHERE rid = 1 ORder by preferred
``` |
| Chart: Multi Standard Deviation | Multi Standard Deviation Source | ```sql
SELECT STDEV(TBLASSAY.sysResult) AS ExpectedStDev,AVG(TBLASSAY.sysResult) AS ExpectedValue,TBLSTANDARDSAMP.StANDardID AS StANDardID FROM(TBLASSAY INNER JOIN TBLSTANDARDSAMP ON(TBLASSAY.SampleID = TBLSTANDARDSAMP.SampleID)AND (TBLASSAY.DataSet = TBLSTANDARDSAMP.DataSet))
WHERE(TBLSTANDARDSAMP.StANDardID in (@Filter_QAQC_StANDards))
AND (TBLASSAY.Element = @Filter_QAQC_Element)
AND TBLASSAY.sysResult IS NOT NULL
AND TBLASSAY.Repeat = '0'
AND TBLASSAY.Batch_No IN(@filtBatchNo)
AND TBLASSAY.GenericMethod in (@genericMethod)
GROUP BY StANDardID
``` |
| Export:
Total Multi Standard Export AND Table data | Total Multi Standard Export Source | ```sql
SELECT ROW_NUMBER() over (ORDER BY ss.StANDardID,ab.Lab_job_date,a.batch_no, a.SampleID) AS ID,a.DataSet,a.LabCode AS LabORatORy,a.Batch_No AS [Batch No.],a.SampleID AS [Sample ID]
,ss.StANDardID AS [StANDard Name],a.GenericMethod AS [Method Name],a.Element,CAST(a.sysResult AS DECIMAL(16,2)) AS [Value],REPLACE(CONVERT(NVARCHAR,ab.Lab_Job_Date, 106), '', '-') AS [Report Date]
,ISNULL(se.Comments,'') AS Comments FROM tblSYSAssBatch ab
INNER JOIN((tblAssay a INNER JOIN tblStANDardSamp ss ON (a.DataSet = ss.DataSet AND a.SampleID = ss.SampleID))
INNER JOIN tblSYSAssElement ON a.Element = tblSYSAssElement.Element ON (ab.batch_no = a.Batch_No) AND (ab.LabCode = a.LabCode)
LEFT OUTER JOIN tblSYSQCSampleEvents se ON a.DataSet = se.DataSet AND a.SampleID = se.SampleID AND a.Element = se.Element AND a.Repeat = se.Repeat AND a.GenericMethod = se.ORigMethod AND a.Batch_No = se.Batch_No AND a.Preferred = se.Preferred
WHERE(ss.StANDardID IN (@Filter_QAQC_StANDards))
AND (a.Element = @Filter_QAQC_Element)
AND a.Repeat = '0'
AND a.Batch_No IN (@filtBatchNo)
AND ((a.Preferred = 0 AND ab.batch_status in ('A','QC_NA'))
OR (a.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND ab.batch_status in ('R','Q')))
AND a.sysResult IS NOT NULL
AND a.GenericMethod IN (@genericMethod)
AND a.DataSet IN (@filtDataset)
ORDER BY ss.StANDardID,ab.Lab_job_date,a.batch_no,a.SampleID;
``` |
| Filters: Repeats Method Filter | Repeat Method Filter Source | ```sql
SELECT distinct a.GenericMethod FROM tblAssay A
INNER JOIN #sampType S ON A.SampleID = S.SampleID AND a.DataSet = s.dataset
WHERE a.Batch_No in (@filtBatchNo)
ORder by a.GenericMethod
``` |
| Filters: Repeat Element Filter | Repeat Element Filter Source | ```sql
SELECT DISTINCT TBLASSAY.Element, e.SystemUnits FROM TBLASSAY
inner JOIN tblSYSAssElement e ON tblAssay.Element = e.Element
inner JOIN #sampType S ON tblassay.dataset = s.dataset AND tblassay.sampleid = S.sampleid
WHERE TBLASSAY.Element IS NOT NULL
AND TBLASSAY.Batch_No IN (@filtBatchNo) AND tblassay.genericMethod in (@dupsGenericMethod)
ORDER BY TBLASSAY.Element
``` |
| Chart: Repeats Chart | Repeat Chart Source | ```sql
IF( '#sampType' = 'tblDHSamp' OR '#sampType' = 'tblStANDardSamp' OR '#sampType' = 'tblPTSamp' OR '#sampType' = 'tblDHSampQC' OR
'#sampType' = 'tblStANDardSampQC' OR '#sampType' = 'tblPTSampQC')
BEGIN
IF('#compType' = 'SampleName')
begin
IF(#useORig=1)
BEGIN
SELECT SampTable.SampleID,UPPER(SampTable.QC_CategORy) AS QC_CategORy,RepeatTable.sysResult AS RepeatResult,ORigTable.sysResult AS ORiginalResult FROM(TBLASSAY AS RepeatTable
INNER JOIN #sampTypeQC AS SampTable ON(RepeatTable.SampleID = SampTable.SampleID) AND (RepeatTable.DataSet = SampTable.DataSet))
INNER JOIN TBLASSAY AS ORigTable ON(ORigTable.Element = RepeatTable.Element) AND (SampTable.DataSet = ORigTable.DataSet) AND (SampTable.ORig_SampleID = ORigTable.SampleID)
INNER JOIN tblSYSAssElement AS ELEM ON ELEM.Element = RepeatTable.Element
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = RepeatTable.labcode AND batRepeat.Batch_No = RepeatTable.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = ORigTable.labcode AND batORig.Batch_No = ORigTable.Batch_No
WHERE (SampTable.QC_CategORy in(@dupsYAxis)) AND (ORigTable.Element = @dupsElement)
AND ORigTable.Batch_No IN(@filtBatchNo) AND RepeatTable.Batch_No IN(@filtBatchNo)
AND (((ORigTable.Preferred = 0 AND batORig.batch_status in ('A','QC_NA'))
AND (RepeatTable.Preferred = 0 AND batRepeat.batch_status in ('A','QC_NA')))
OR ((ORigTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q'))
AND (RepeatTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q')))) AND ORigTable.sysResult >= 0.0 AND RepeatTable.sysResult >= 0.0
AND RepeatTable.Repeat = '0' AND ORigTable.Repeat = '0' AND ORigTable.GenericMethod in(@dupsGenericMethod) AND (ORigTable.GenericMethod = RepeatTable.GenericMethod)
END
IF(#useORig=0)
BEGIN
SELECT SampTable.SampleID,UPPER(SampTable.QC_CategORy) AS QC_CategORy,RepeatTable.sysResult AS RepeatResult,ORigTable.sysResult AS ORiginalResult FROM TBLASSAY AS RepeatTable
INNER JOIN TBLASSAY AS ORigTable ON(RepeatTable.Element = ORigTable.Element)
INNER JOIN #sampTypeQC AS SampTable ON ORigTable.DataSet = SampTable.DataSet AND ORigTable.SampleID = SampTable.SampleID
INNER JOIN #sampTypeQC AS SampTable2 ON(SampTable.DataSet = SampTable2.DataSet AND SampTable.ORig_SampleID = SampTable2.ORig_SampleID AND RepeatTable.DataSet = SampTable2.DataSet AND RepeatTable.SampleID = SampTable2.SampleID)
INNER JOIN tblSYSAssElement AS ELEM ON ELEM.Element = RepeatTable.Element
WHERE SampTable.SampleID <> SampTable2.SampleID
AND SampTable.QC_CategORy in(@dupsXAxis)
AND SampTable2.QC_CategORy in(@dupsYAxis)
AND RepeatTable.Element = @dupsElement
AND ORigTable.sysResult >= 0.0
AND RepeatTable.sysResult >= 0.0
AND RepeatTable.Repeat = '0'
AND ORigTable.Repeat = '0'
AND ORigTable.Batch_No IN(@filtBatchNo)
AND RepeatTable.Batch_No IN(@filtBatchNo)
AND ORigTable.GenericMethod in(@dupsGenericMethod)
AND (ORigTable.GenericMethod = RepeatTable.GenericMethod)
END
END
IF('#compType' = 'RepeatCode')
begin
IF(#useORig=1)
BEGIN SELECT ORigTable.SampleID,UPPER(RepeatTable.Repeat) AS QC_CategORy,ORigTable.sysResult AS ORiginalResult,RepeatTable.sysResult AS RepeatResult FROM #sampType AS SampTable
INNER JOIN TBLASSAY AS ORigTable ON SampTable.DataSet = ORigTable.DataSet AND SampTable.SampleID = ORigTable.SampleID
INNER JOIN TBLASSAY AS RepeatTable ON ORigTable.Batch_No = RepeatTable.Batch_No AND ORigTable.SampleID = RepeatTable.SampleID AND ORigTable.DataSet = RepeatTable.DataSet
INNER JOIN tblSYSAssElement AS ELEM ON ELEM.Element = ORigTable.Element
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = RepeatTable.labcode AND batRepeat.Batch_No = RepeatTable.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = ORigTable.labcode AND batORig.Batch_No = ORigTable.Batch_No
WHERE(ORigTable.Element = RepeatTable.Element)
AND (ORigTable.Element = @dupsElement)
AND (ORigTable.Repeat = '0')
AND (RepeatTable.Repeat in (@dupsYAxis))
AND ORigTable.Batch_No IN(@filtBatchNo)
AND (((ORigTable.Preferred = 0 AND batORig.batch_status in ('A','QC_NA'))
AND (RepeatTable.Preferred = 0 AND batRepeat.batch_status in ('A','QC_NA')))
OR ((ORigTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q'))
AND (RepeatTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q'))))
AND ORigTable.sysResult >= 0.0
AND RepeatTable.sysResult >= 0.0
AND ORigTable.GenericMethod in(@dupsGenericMethod)
AND (ORigTable.GenericMethod = RepeatTable.GenericMethod)
end
if(#useORig=0)
begin
SELECT ORigTable.SampleID,UPPER(RepeatTable.Repeat) AS QC_CategORy,ORigTable.sysResult AS ORiginalResult,RepeatTable.sysResult AS RepeatResult FROM #sampType AS SampTable
INNER JOIN TBLASSAY AS ORigTable ON SampTable.DataSet = ORigTable.DataSet
AND SampTable.SampleID = ORigTable.SampleID
INNER JOIN TBLASSAY AS RepeatTable ON ORigTable.Batch_No = RepeatTable.Batch_No
AND ORigTable.SampleID = RepeatTable.SampleID
AND ORigTable.DataSet = RepeatTable.DataSet
INNER JOIN tblSYSAssElement AS ELEM ON ELEM.Element = ORigTable.Element
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = RepeatTable.labcodeAND batRepeat.Batch_No = RepeatTable.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = ORigTable.labcode AND batORig.Batch_No = ORigTable.Batch_No
WHERE(ORigTable.Element = RepeatTable.Element) AND (ORigTable.Element = @dupsElement) AND (ORigTable.Repeat in(@dupsXAxis))
AND (RepeatTable.Repeat in(@dupsYAxis)) AND ORigTable.Batch_No IN(@filtBatchNo)
AND (((ORigTable.Preferred = 0 AND batORig.batch_status in ('A','QC_NA'))
AND (RepeatTable.Preferred = 0 AND batRepeat.batch_status in ('A','QC_NA')))
OR ((ORigTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q'))
AND (RepeatTable.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q'))))
AND ORigTable.sysResult >= 0.0
AND RepeatTable.sysResult >= 0.0
AND ORigTable.GenericMethod IN (@dupsGenericMethod)
AND (ORigTable.GenericMethod = RepeatTable.GenericMethod)
end
end
END
``` |
| Export: Total Repeat Export AND Table data | Total Repeat Export Source | ```sql
IF ('#sampType' = 'tblDHSamp' OR '#sampType' = 'tblStANDardSamp' OR '#sampType' = 'tblPTSamp' OR
'#sampType' = 'tblDHSampQC' OR '#sampType' = 'tblStANDardSampQC' OR '#sampType' = 'tblPTSampQC')
BEGIN
IF ('#compType' = 'SampleName')
BEGIN
IF (#useORig=1)
BEGIN
SELECT s.DataSet, s.Hole_ID AS [Hole ID], s.ORig_SampleID AS [ORignal Sample ID], s.SampleID AS [Repeat Sample ID],a2.LabCode AS [ORignal Lab Code],a1.LabCode AS [Repeat Lab Code],a2.Batch_No AS [ORignal Batch No],a1.Batch_No AS [Repeat Batch No],a2.GenericMethod AS [ORignal Method],a1.GenericMethod AS [Repeat Method], CONCAT(CAST(a2.sysResult AS DECIMAL(16,2)), ' ppm') AS [ORignal Result], CONCAT(CAST(a1.sysResult AS DECIMAL(16,2)), ' ppm') AS [Repeat Result], CASE WHEN (a1.sysResult = a2.sysResult) THEN '0%' ELSE CONCAT(FORMAT(((a1.sysResult - a2.sysResult) / a2.sysResult) * 100, N'#.#'), '%') END AS [Difference], ISNULL(se.Comments,'') AS Comments
FROM (TBLASSAY AS a1
INNER JOIN tblDHSampQC AS s ON (a1.SampleID = s.SampleID AND a1.DataSet = s.DataSet))
INNER JOIN TBLASSAY AS a2 ON (a2.Element = a1.Element) AND (s.DataSet = a2.DataSet) AND (s.ORig_SampleID = a2.SampleID)
INNER JOIN tblSYSAssElement AS e ON (e.Element = a1.Element)
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = a1.labcode AND batRepeat.Batch_No = a1.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = a2.labcode AND batORig.Batch_No = a2.Batch_No
LEFT OUTER JOIN tblSYSQCSampleEvents se ON (a2.DataSet = se.DataSet AND a2.SampleID = se.SampleID AND a2.Element = se.Element AND a2.Repeat = se.Repeat AND a2.GenericMethod = se.ORigMethod AND a2.Batch_No = se.Batch_No AND a2.Preferred = se.Preferred)
WHERE a2.Element = @dupsElement
AND s.QC_CategORy IN (@dupsYAxis)
AND a2.Batch_No IN (@filtBatchNo)
AND a1.Batch_No IN (@filtBatchNo)
AND (((a2.Preferred = 0 AND batORig.batch_status in ('A','QC_NA'))
AND (a1.Preferred = 0 AND batRepeat.batch_status in ('A','QC_NA')))
OR ((a2.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q')) AND (a1.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q'))))
AND a2.sysResult >= 0.0 AND a1.sysResult >= 0.0 AND a1.Repeat = '0'
AND a2.Repeat = '0' AND a2.GenericMethod IN (@dupsGenericMethod) AND a2.GenericMethod = a1.GenericMethod
END
IF (#useORig=0)
BEGIN
SELECT s.DataSet,s.Hole_ID AS [Hole ID], s.ORig_SampleID AS [ORignal Sample ID], s.SampleID AS [Repeat Sample ID],a2.LabCode AS [ORignal Lab Code], a1.LabCode AS [Repeat Lab Code], a2.Batch_No AS [ORignal Batch No],a1.Batch_No AS [Repeat Batch No],a2.GenericMethod AS [ORignal Method], a1.GenericMethod AS [Repeat Method],CONCAT(CAST(a2.sysResult AS DECIMAL(16,2)), ' ppm') AS [ORignal Result], CONCAT(CAST(a1.sysResult AS DECIMAL(16,2)), ' ppm') AS [Repeat Result],CASE WHEN (a1.sysResult = a2.sysResult) THEN '0%' ELSE CONCAT(FORMAT(((a1.sysResult - a2.sysResult) / a2.sysResult) * 100, N'#.#'), '%') END AS [Difference], ISNULL(se.Comments,'') AS Comments
FROM TBLASSAY AS a1
INNER JOIN TBLASSAY AS a2 ON (a1.Element = a2.Element)
INNER JOIN tblDHSampQC AS s ON a2.DataSet = s.DataSet AND a2.SampleID = s.SampleID
INNER JOIN tblDHSampQC AS SampTable2 ON (s.DataSet = SampTable2.DataSet AND s.ORig_SampleID = SampTable2.ORig_SampleID AND a1.DataSet = SampTable2.DataSet
AND a1.SampleID = SampTable2.SampleID)
INNER JOIN tblSYSAssElement AS e ON (e.Element = a1.Element)
LEFT OUTER JOIN tblSYSQCSampleEvents se ON (a2.DataSet = se.DataSet AND a2.SampleID = se.SampleID AND a2.Element = se.Element AND a2.Repeat = se.Repeat AND a2.GenericMethod = se.ORigMethod AND a2.Batch_No = se.Batch_No AND a2.Preferred = se.Preferred)
WHERE s.SampleID <> SampTable2.SampleID
AND s.QC_CategORy IN (@dupsXAxis)
AND SampTable2.QC_CategORy IN (@dupsYAxis)
AND a1.Element = @dupsElement
AND a2.sysResult >= 0.0
AND a1.sysResult >= 0.0
AND a1.Repeat = '0'
AND a2.Repeat = '0'
AND a2.Batch_No IN (@filtBatchNo)
AND a1.Batch_No IN (@filtBatchNo)
AND a2.GenericMethod IN (@dupsGenericMethod)
AND a2.GenericMethod = a1.GenericMethod
END
END
IF ('#compType' = 'RepeatCode')
BEGIN
IF (#useORig=1)
BEGIN
SELECT s.DataSet, s.Hole_ID AS [Hole ID],a2.SampleID AS [ORignal Sample ID],s.SampleID AS [Repeat Sample ID], a2.LabCode AS [ORignal Lab Code],a1.LabCode AS [Repeat Lab Code],a2.Batch_No AS [ORignal Batch No], a1.Batch_No AS [Repeat Batch No], a2.GenericMethod AS [ORignal Method],a1.GenericMethod AS [Repeat Method], CONCAT(CAST(a2.sysResult AS DECIMAL(16,2)), ' ppm') AS [ORignal Result], CONCAT(CAST(a1.sysResult AS DECIMAL(16,2)), ' ppm') AS [Repeat Result], CASE WHEN (a1.sysResult = a2.sysResult) THEN '0%' ELSE CONCAT(FORMAT(((a1.sysResult - a2.sysResult) / a2.sysResult) * 100, N'#.#'), '%') END AS [Difference], ISNULL(se.Comments,'') AS Comments
FROM tblDHSamp AS s
INNER JOIN TBLASSAY AS a1 ON (s.DataSet = a1.DataSet AND s.SampleID = a1.SampleID)
INNER JOIN TBLASSAY AS a2 ON (a1.Batch_No = a2.Batch_No AND a1.SampleID = a2.SampleID AND a1.DataSet = a2.DataSet)
INNER JOIN tblSYSAssElement AS e ON (e.Element = a1.Element)
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = a1.labcode AND batRepeat.Batch_No = a1.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = a2.labcode AND batORig.Batch_No = a2.Batch_No
LEFT OUTER JOIN tblSYSQCSampleEvents se ON (a2.DataSet = se.DataSet AND a2.SampleID = se.SampleID AND a2.Element = se.Element AND a2.Repeat = se.Repeat AND a2.GenericMethod = se.ORigMethod AND a2.Batch_No = se.Batch_No AND a2.Preferred = se.Preferred)
WHERE a1.Element = a2.Element
AND a1.Element = @dupsElement
AND a1.Repeat = '0'
AND a2.Repeat in (@dupsYAxis)
AND a1.Batch_No IN (@filtBatchNo)
AND (((a2.Preferred = 0
AND batORig.batch_status in ('A','QC_NA'))
AND (a1.Preferred = 0 AND batRepeat.batch_status in ('A','QC_NA')))
OR ((a2.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q'))
AND (a1.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q'))))
AND a1.sysResult >= 0.0
AND a2.sysResult >= 0.0
AND a1.GenericMethod IN (@dupsGenericMethod)
AND a1.GenericMethod = a2.GenericMethod
END
IF (#useORig=0)
BEGIN
SELECT s.DataSet,s.Hole_ID AS [Hole ID], a1.SampleID AS [ORignal Sample ID],s.SampleID AS [Repeat Sample ID],a2.LabCode AS [ORignal Lab Code], a1.LabCode AS [Repeat Lab Code],a2.Batch_No AS [ORignal Batch No],a1.Batch_No AS [Repeat Batch No], a2.GenericMethod AS [ORignal Method],a1.GenericMethod AS [Repeat Method], CONCAT(CAST(a2.sysResult AS DECIMAL(16,2)), ' ppm') AS [ORignal Result], CONCAT(CAST(a1.sysResult AS DECIMAL(16,2)), ' ppm') AS [Repeat Result],CASE WHEN (a1.sysResult = a2.sysResult) THEN '0%'ELSE CONCAT(FORMAT(((a1.sysResult - a2.sysResult) / a2.sysResult) * 100, N'#.#'), '%') END AS [Difference], ISNULL(se.Comments,'') AS Comments
FROM tblDHSamp AS s
INNER JOIN TBLASSAY AS a2 ON s.DataSet = a2.DataSet AND s.SampleID = a2.SampleID
INNER JOIN TBLASSAY AS a1 ON (a2.Batch_No = a1.Batch_No AND a2.SampleID = a1.SampleID AND a2.DataSet = a1.DataSet)
INNER JOIN tblSYSAssElement AS e ON (e.Element = a2.Element)
INNER JOIN tblSYSAssBatch AS batRepeat ON batRepeat.labcode = a1.labcode AND batRepeat.Batch_No = a1.Batch_No
INNER JOIN tblSYSAssBatch AS batORig ON batORig.labcode = a2.labcode AND batORig.Batch_No = a2.Batch_No
LEFT OUTER JOIN tblSYSQCSampleEvents se ON (a2.DataSet = se.DataSet AND a2.SampleID = se.SampleID AND a2.Element = se.Element AND a2.Repeat = se.Repeat AND a2.GenericMethod = se.ORigMethod AND a2.Batch_No = se.Batch_No AND a2.Preferred = se.Preferred)
WHERE a2.Element = a1.Element
AND a2.Element = @dupsElement
AND a2.Repeat IN (@dupsXAxis)
AND a1.Repeat IN (@dupsYAxis)
AND a2.Batch_No IN (@filtBatchNo)
AND (((a2.Preferred = 0
AND batORig.batch_status in ('A','QC_NA'))
AND (a1.Preferred = 0
AND batRepeat.batch_status in ('A','QC_NA')))
OR ((a2.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batORig.batch_status in ('R','Q'))
AND (a1.Preferred = (SELECT param1 FROM tblsysparam WHERE code = 'AssayMergeReslutPreferredValue')
AND batRepeat.batch_status in ('R','Q'))))
AND a2.sysResult >= 0.0
AND a1.sysResult >= 0.0
AND a2.GenericMethod IN (@dupsGenericMethod)
AND a2.GenericMethod = a1.GenericMethod
END
END
END;
``` |