WITH Exceedance(Chemical, Unit, QualityStandard, SampleDate, WellId, Result, MaxValue) AS
(
SELECT
a.[Name], a.[Abbreviation], t.[Name], s.[SampleDate], s.[WellId], sr.[ConvertedResultValue], q.[MaxValue]
--,NTILE(4) OVER(PARTITION BY a.[Name] ORDER BY sr.[ConvertedResultValue] DESC) AS 'Quartile'
FROM [dbo].[Sample] AS s
INNER JOIN [dbo].[SampleResult] AS sr ON s.[SampleId] = sr.[SampleId]
INNER JOIN [dbo].[vwAnalytes] AS a ON sr.[AnalyteId] = a.[AnalyteId]
INNER JOIN [dbo].[WaterQualityStandard] AS q ON a.[AnalyteId] = q.[AnalyteId]
INNER JOIN [dbo].[WaterQualityStardardType] AS t ON q.[WaterQualityStandardTypeId] = t.[WaterQualityStandardTypeId]
INNER JOIN [dbo].[CSVToTable](@@Wells) AS p ON s.WellId = p.Id
WHERE
-- Omit CA DLR (California Detection Limit for purposes of Reporting)
t.[WaterQualityStandardTypeId] <> 5 AND
s.[SampleDate] BETWEEN @@Start AND @@End
)
-- Now we PIVOT the CTE on the QualityStandard
SELECT ExPivot.Chemical,
ExPivot.Unit,
[Primary EPA MCL] = [Primary EPA MCL],
[Secondary EPA MCL] = [Secondary EPA MCL],
[Primary CA MCL] = [Primary CA MCL],
[Secondary CA MCL] = [Secondary CA MCL],
[CA NL] = [CA NL],
MIN(Result) AS [Min],
(SELECT MAX(q1.Result)
FROM (
SELECT e.Result,
NTILE(4) OVER(PARTITION BY e.Chemical ORDER BY e.Result) AS Quartile
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical
AND e.Result IS NOT NULL
) AS q1
WHERE q1.Quartile = 1
)
AS [1st Quartile],
(SELECT MAX(q.Result) FROM (SELECT TOP 50 PERCENT e.Result
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical
AND e.Result IS NOT NULL
ORDER BY e.Result) AS q) AS [Median],
(SELECT MAX(q1.Result)
FROM (
SELECT e.Result,
NTILE(4) OVER(PARTITION BY e.Chemical ORDER BY e.Result) AS Quartile
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical
AND e.Result IS NOT NULL
) AS q1
WHERE q1.Quartile = 3
)
AS [3rd Quartile],
MAX(Result) AS [Max],
AVG(Result) AS Average,
COUNT(*) AS [Total Number of Samples],
(SELECT COUNT(DISTINCT e.WellId)
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical) AS [Number of Wells Sampled],
(SELECT COUNT(DISTINCT e.WellId)
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical
AND e.Result IS NOT NULL) AS [Number of Wells with Detects],
(SELECT COUNT(DISTINCT e.WellId)
FROM Exceedance AS e
WHERE e.Chemical = ExPivot.Chemical
AND e.Result IS NOT NULL
AND e.MaxValue IS NOT NULL
AND e.Result > e.MaxValue) AS [Number of Wells with Exceedances]
FROM Exceedance
PIVOT (
MAX(MaxValue)
FOR QualityStandard IN ([Primary EPA MCL], [Secondary EPA MCL], [Primary CA MCL], [Secondary CA MCL], [CA NL])
) AS ExPivot
GROUP BY ExPivot.Chemical,
ExPivot.Unit,
[Primary EPA MCL],
[Secondary EPA MCL],
[Primary CA MCL],
[Secondary CA MCL],
[CA NL]
ORDER BY ExPivot.Chemical