The SQL scripts below are examples that were used during the Advanced Data Validation session. These examples demonstrate different tecniques in creating custom definitions but may need to be adjusted for your use.


EXAMPLE 1:
-------------------------------AERIES004 - updated for flex scheduling-------------------------------
SELECT     
STU.SC AS [SchoolCode],     
STU.ID AS [Student ID],     
'' AS [Description]    
FROM STU    
WHERE STU.DEL = 0 
AND STU.TG = '' 
AND STU.CU = 0 
AND STU.SC NOT IN (SELECT SC FROM OPT WHERE OPT.NM = 'UseStaffIDBasedCounselorTracking' AND OPT.CD = 'True')
AND STU.SC = @SchoolCode 
AND STU.ID IN (@StudentID)

UNION ALL 

SELECT     
STU.SC AS [SchoolCode],     
STU.ID AS [Student ID],     
'' AS [Description]    
FROM STU    
WHERE STU.DEL = 0 
AND STU.TG = '' 
AND STU.CNS = 0 
AND STU.SC IN (SELECT SC FROM OPT WHERE OPT.NM = 'UseStaffIDBasedCounselorTracking' AND OPT.CD = 'True')
AND STU.SC = @SchoolCode 
AND STU.ID IN (@StudentID)




EXAMPLE 2:
-------------------------------Old SSA Date-------------------------------
SELECT 
STU.ID [StudentID],
'SSA date is more than 3 years old - ' + CONVERT(VARCHAR,SD,101) [Description]
FROM STU 
WHERE STU.DEL = 0
AND STU.TG = ''
AND STU.SD < GETDATE() - 1096
AND STU.ID IN (@StudentID)
AND STU.SC = (@SchoolCode)




EXAMPLE 3:
-------------------------------Unresolved Discipline Errors------------------------------- 
SELECT DVR.SCL [School], 
CONVERT(VARCHAR,COUNT(DVD.DE)) + ' ' + DVG.DE + ' Errors at ' + LOC.NM [Description] 
FROM 
(SELECT * FROM DVR WHERE DEL = 0) DVR   
	INNER JOIN (SELECT * FROM DVD WHERE DEL = 0) DVD ON DVR.VID = DVD.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 DVG.DE = 'Assertive Discipline'  
AND DVR.DT < GETDATE() - 14
GROUP BY DVR.SCL, LOC.NM, DVG.DE




EXAMPLE 4:
-------------------------------ADS Record missing weapon type-------------------------------
DECLARE @July1 datetime = CONVERT(varchar(4), CASE WHEN MONTH(GETDATE()) > 6 THEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) - 1 END) + '-07-01'

SELECT 
STU.ID [Student ID],  
ADS.SRF [Referrer],
--ADS.SQ [SQ?], 
'Incident Date ' + CONVERT(varchar, ADS.DT, 101) [Description]
FROM STU 
	INNER JOIN ADS ON STU.ID = ADS.PID     
    INNER JOIN XRF ON ADS.CD = XRF.CD1
WHERE STU.DEL = 0 AND ADS.DEL = 0              
AND ADS.WT = '' 
AND STU.SC = ADS.SCL 
AND XRF.TC = 'CLPD' 
AND XRF.TC1 = 'ADS' 
AND XRF.FC1 = 'CD' 
AND XRF.CD IN ('100','101','102','105')
AND ADS.DT > @July1
AND STU.SC = @SchoolCode 
AND STU.ID IN (@StudentID)




EXAMPLE 5:
-------------------------------AERIES020 updated for flex------------------------------- 
SELECT mst.sc [SchoolCode], 
			mst.se [Section Number],
'Course: ' + CRS.CO + ' (' + CRS.CN + ')' [Description],
			mst.se [SE?]
		FROM mst   
			INNER JOIN crs ON mst.cn = crs.cn
			LEFT JOIN (SELECT * FROM SSE WHERE DEL = 0 AND PR = 1) SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE
WHERE MST.DEL = 0 AND
	((mst.tn = 0 AND MST.SC NOT IN (SELECT SC FROM OPT WHERE NM = 'SchedUseStaff' AND CD = 'True')) 
OR 
	(SSE.ID IS NULL AND MST.SC IN (SELECT SC FROM OPT WHERE NM = 'SchedUseStaff' AND CD = 'True')))
			AND mst.sc = @SchoolCode




EXAMPLE 6:
-------------------------------AERIES022 updated for flex------------------------------- 
SELECT SMS.sc [SchoolCode], 
			SMS.se [Section Number],
'Course: ' + CRS.CO + ' (' + CRS.CN + ')' [Description],
			SMS.se [SE?]
		FROM SMS   
			INNER JOIN crs ON SMS.cn = crs.cn
			LEFT JOIN (SELECT * FROM SSM WHERE DEL = 0 AND PR = 1) SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE
WHERE SMS.DEL = 0 AND
	((SMS.tn = 0 AND SMS.SC NOT IN (SELECT SC FROM OPT WHERE NM = 'SchedUseStaff' AND CD = 'True')) 
OR 
	(SSM.ID IS NULL AND SMS.SC IN (SELECT SC FROM OPT WHERE NM = 'SchedUseStaff' AND CD = 'True')))
			AND SMS.sc = @SchoolCode




EXAMPLE 7:
-------------------------------US School 3& up is blank-------------------------------
SELECT 
STU.ID [Student ID], 
'' [Description]
FROM STU 
INNER JOIN SSD ON STU.SC = SSD.SC AND STU.SN = SSD.SN 
	INNER JOIN LAC ON STU.ID = LAC.ID
   WHERE LAC.DEL = 0 
   AND STU.DEL = 0 
   AND SSD.DEL = 0
   AND LAC.US3 IS NULL
   AND SSD.SUS = 'Y'
   AND STU.ID IN (@StudentID)
   AND STU.SC = @SchoolCode




EXAMPLE 8:
-------------------------------Incorrect SSA Date (multiple scenarios)-------------------------------
SELECT 
STU.ID [Student ID], 
STU.SD [SSA Date], 
CASE 
WHEN SSA.DT IS NOT NULL AND STU.SD IS NULL THEN 'SSA date is blank - ' + CONVERT(VARCHAR,SSA.DT,101)
WHEN SSA.DT <> STU.SD THEN 'Incorrect SSA Date - ' + CONVERT(VARCHAR,SSA.DT,101)
WHEN SSA.DT IS NULL AND STU.SD IS NOT NULL THEN 'Expired SSA Date'
END
 [Description]  
FROM STU   
LEFT JOIN   (   
	SELECT PID ID, MAX(ADS.DT) DT
	FROM ADS
	WHERE DEL = 0 AND CD IN (SELECT CD FROM SOC WHERE SSA <> '')
	GROUP BY PID
	HAVING MAX(DT) > GETDATE () - 1096) SSA ON SSA.ID = STU.ID  
WHERE STU.DEL = 0 
AND ((SSA.DT <> STU.SD) OR (SSA.DT IS NOT NULL AND STU.SD IS NULL) OR STU.SD < GETDATE() - 1096)   
AND STU.ID IN (@StudentID)
AND STU.SC = @SchoolCode