IIf statement
Jamie Collins wrote:
The proposed solution snipped
After peeking at John Spencer's proposal:
SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;
Jamie.
--
|