How to Query the total number of unresolved Data Validation Results by Data Validation Definition, grouped by school.  


Tables used:

LOC - school name

DVD - Data Validation Definition properties

DVR - Data Validation Results

DVG - Data Validation Groups


Conditions:

DVR.DR IS NULL - exclude records with a Resolved Date 

DVD.DS = 0 - exclude results of disabled definitions

DVG.DS = 0 - exclude results of disabled groups


Aeries Query:

TOTAL DVR DVD DVG LOC LOC.NM DVD.DE BY LOC.NM DVD.DE IF DVR.DR = NULL AND DVD.DS = 0 AND DVG.DS = 0


SQL Query:

SELECT 
LOC.NM, 
DVD.DE, 
COUNT(*) [Results]
FROM DVD 
     INNER JOIN (SELECT * FROM DVR WHERE DEL = 0) DVR ON DVD.VID = DVR.VID
     INNER JOIN (SELECT * FROM DVG WHERE DEL = 0) DVG ON DVD.GID = DVG.GID
     INNER JOIN (SELECT * FROM LOC WHERE DEL = 0) LOC ON DVR.SCL = LOC.CD 
WHERE DVR.DR IS NULL
AND DVD.DS = 0
AND DVG.DS = 0
GROUP BY LOC.NM, DVD.DE 
ORDER BY LOC.NM, DVD.DE, COUNT(*)