Tool Search: Filter Designer
The following queries are related to student information and require that the Student radio button be selected from the main Filter Designer page.
These sample queries are for example purposes only. They are a starting point to give an idea of how this tool can be used. A request for Campus Support to design, modify, or troubleshoot a Pass-Through Query is a request for Custom Development, which is subject to a fee.
Student pass-through queries inherit the year, school, calendar and schedule selected in the Campus toolbar.
Student Queries for Census and Demographic Data
Student Birth Date from Range
This query returns students whose birth dates exist within a range of time between two dates. It includes student birth dates occurring on the specified start or end date.
Box A
No text necessaryBox B
AND student.birthdate BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'Student Birthdays by Week
This query lists students who have a birthday during the current week (Monday through Sunday).
Box A
No text necessaryBox B
AND student.activeyear=1
AND student.startdate<=getdate()
AND (student.enddate>=getdate() OR student.enddate is NULL)
AND DATEADD( Year,DATEPART( Year, GETDATE()) - DATEPART( Year, student.birthdate), student.birthdate)
BETWEEN dateadd(wk,datediff(wk,0,getdate()),0)
AND dateadd(wk,datediff(wk,0,getdate()),6)Students without a Current Household
This query returns students who do not have a current household indicated.
Box A
LEFT OUTER JOIN HouseholdMember hm ON hm.personID = student.personID
AND (hm.endDate IS NULL OR hm.endDate > getDate())Box B
AND hm.personID IS NULLStudents in a Household by Themselves
This query returns students who are in a household with no other members.
Box A
INNER JOIN HouseholdMember hm1 ON hm1.personID = student.personID
AND (hm1.startDate IS NULL OR hm1.startDate <= getDate()) AND (hm1.endDate IS NULL OR hm1.endDate > getDate())
INNER JOIN HouseholdMember hm2 ON hm2.householdID = hm1.householdID
AND (hm2.startDate IS NULL OR hm2.startDate <= getDate())AND (hm2.endDate IS NULL OR hm2.endDate > getDate())Box B
GROUP BY student.personID, student.lastname, student.firstname, student.grade, student.studentnumber
HAVING COUNT(hm2.memberID) = 1Students in Households with a Combined Total of More than Five
This query returns students who are in a household of more than five members.
Box A
INNER JOIN HouseholdMember hm1 ON hm1.personID = student.personID
AND (hm1.startDate IS NULL OR hm1.startDate <=getDate()) AND (hm1.endDate IS NULL OR hm1.endDate >getDate ())
INNER JOIN HouseholdMember hm2 ON hm2.householdID = hm1.householdID
AND (hm2.startDate IS NULL OR hm2.startDate <= getDate ()) AND (hm2.endDate IS NULL OR hm2.endDate > getDate())Box B
GROUP BY student.personID, student.lastname, student.firstname, student.grade, student.studentnumber, hm1.householdID
HAVING COUNT(hm2.memberID) > 5Students in a Household with no Address
This query returns students who have no address assigned to their household(s).
Box A
INNER JOIN HouseholdMember HM ON HM.personID=student.personID
INNER JOIN household H on h.householdID=HM.householdID
LEFT OUTER JOIN HouseholdLocation L on L.householdID=H.householdIDBox B
AND L.addressID IS NULLStudent not in any Household with a Mailing Address - Includes Secondary Addresses
This query returns students who are not in any household with a mailing address. If a student has three addresses, where one of them is marked as a mailing address but the other two are not, the student is NOT returned by the query.
Box A
LEFT OUTER JOIN HouseholdMember hm ON hm.personID = student.personID AND (hm.endDate IS NULL OR hm.endDate > getDate())
LEFT OUTER JOIN HouseholdLocation hl ON hl.householdID = hm.householdID
AND (hl.endDate IS NULL OR hl.endDate > getDate())
AND hl.mailing = 1Box B
AND hl.locationID IS NULLStudents not in any Household with a Mailing Address - Excludes Secondary Household and Secondary Addresses
This query returns students who are not in any household with a mailing address. If a student has three addresses, where one of them is marked as a mailing address but the other two are not, the student is NOT returned by the query.
Box A
LEFT OUTER JOIN HouseholdMember hm ON hm.personID = student.personID AND (hm.endDate IS NULL OR hm.endDate > getDate())
AND (hm.secondary IS NULL OR hm.secondary = 0)
LEFT OUTER JOIN HouseholdLocation hl ON hl.householdID = hm.householdID
AND (hl.endDate IS NULL OR hl.endDate > getDate())
AND (hl.secondary IS NULL OR hl.secondary = 0) AND hl.mailing = 1Box B
AND hl.locationID IS NULLStudents with More Than One Primary Mailing Address
This query returns students who have more than one primary mailing address listed. This query pulls from the Student View and considers the endYear. Students who have more than one enrollment in multiple schools in the school year report twice.
Box A
INNER JOIN HouseholdMember hm ON hm.personID = student.personID
AND (hm.endDate IS NULL OR hm.endDate > getDate()) AND (hm.secondary IS NULL OR hm.secondary = 0)
INNER JOIN HouseholdLocation hl ON hl.householdID = hm.householdID
AND (hl.endDate IS NULL OR hl.endDate > getDate())AND (hl.secondary IS NULL OR hl.secondary = 0) AND hl.mailing = 1Box B
GROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumber HAVING COUNT(hl.locationID) > 1Student Queries for Course Information
Students who did not make a course request for a certain type of course by Course Group
This query returns a list of students who did not make a course request for a type of course, like Language Arts or Math. Modify the name of the course group (in the query as Language Arts) to meet the needs of your district. Ensure the query is returning the credit name, not the department name.
Box A
LEFT JOIN (Request r
JOIN Course crs ON crs.courseID = r.courseID
JOIN GradingTaskCredit gtc ON gtc.courseID = crs.courseID
JOIN CurriculumStandard cs ON cs.standardID = gtc.standardID And cs.name = 'Language Arts')
ON r.personID = student.personID And student.calendarID = crs.calendarIDBox B
AND crs.number IS NULLStudents who did not make a course request for a certain type of course by Department
This query returns a list of students who did not make a course request for a type of course, like Language Arts or Math. Modify the name of the Department (in the query as Language Arts) to meet the needs of your district.
Box A
LEFT JOIN (Request r join Course crs ON crs.courseID = r.courseID
JOIN Department d ON d.departmentID = crs.departmentID And d.name = 'Language Arts') ON r.personID = student.personID AND student.calendarID = crs.calendarIDBox B
AND crs.number IS NULL Students Taking a Course
This query returns students who are taking a course, as specified. Use Box B to indicate a specific course or a general type of course (e.g., specifying "%Literature%" pulls all course names containing the word "literature").
Box A
INNER JOIN Roster r ON r.personID = student.personID
INNER JOIN Section s ON s.sectionID = r.sectionID
INNER JOIN Course c ON c.courseID = s.courseID
AND c.calendarID = student.calendarID
INNER JOIN Trial t ON t.trialID = s.trialID AND t.active = 1Box B
AND c.name LIKE '%Literature%'Students Registered for Part of a Course
This query returns students who have requested only one semester of a year-long course, instead of both semesters (e.g., if Algebra is a year-long course, students registered for Semester 1 but not Semester 2, or vice versa, are returned). The course number must be specified.
Box A
INNER JOIN Course c1 ON c1.calendarID = student.calendarID AND c1.number = '1300'LEFT OUTER JOIN Request r1 ON r1.personID = student.personID AND r1.courseID=c1.courseIDINNER JOIN Course c2 ON c2.calendarID = student.calendarID AND c2.number = '1350'LEFT OUTER JOIN Request r2 ON r2.personID = student.personID AND r2.courseID=c2.courseID
Box B
AND ((r1.requestID IS NULL
AND r2.requestID IS NOT NULL)
OR (r2.requestID IS NULL AND r1.requestID IS NOT NULL))Students who did not get a Requested Course
This query returns students who did not get scheduled into a course they requested. The course number must be specified.
Box A
JOIN Request r ON r.personID = student.personID And r.calendarID = student.calendarID
JOIN Course crs ON crs.courseID = r.courseID
LEFT JOIN (Roster ros JOIN Section sec ON sec.sectionID = ros.sectionID JOIN Trial trl ON trl.trialID = sec.trialID AND trl.active = 1) ON ros.personID = r.personID AND sec.courseID = r.courseIDBox B
AND r.type IN ('R','E') AND ros.personID IS NULL
AND crs.number = '1234'Students who have Less Than X Requested Units
This query returns students who have less than a certain number of requested units.
Box A
INNER JOIN Request r ON r.personID = student.personID
INNER JOIN Course c ON c.courseID = r.courseIDBox B
AND r.type IN('R','E') AND r.calendarID = student.calendarIDGROUP by student.personID, student.lastName, student.firstName, student.grade, student.studentNumberHAVING(SUM(c.terms*c.schedules*c.periods))< 48
Students Who Have More Than One Course In a Period
This query returns students who are scheduled into more than one course in a period. This only works before the start of the calendar.
Box A
INNER JOIN Roster ro ON ro.personID = student.personID and ro.enddate IS NULL INNER JOIN Section se ON se.SectionID = ro.sectionID INNER JOIN Course co ON co.courseID = se.courseID AND co.calendarID = student.calendarID INNER JOIN SectionPlacement sp ON sp.sectionID = se.sectionID INNER JOIN Period p ON p.periodID = sp.periodID INNER JOIN PeriodSchedule ps ON ps.periodScheduleID = p.periodScheduleID INNER JOIN Trial tr ON tr.trialID = se.trialID AND tr.active = 1
Box B
GROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumber, p.periodID, ps.periodScheduleID, sp.termID HAVING COUNT(se.sectionID) > 1
Students in a Blended Learning Group with no Participation Record
This query returns a list of students who are in a Blended Learning Group on the current day (virtual today) but there is no participation record for the student (e.g., the student did not check in or the teacher did not mark the student as participated).
Box A
INNER JOIN Roster ro on ro.personID = student.personID
INNER JOIN Section se on se.sectionID = ro.sectionID
INNER JOIN Trial tr on tr.trialID = se.trialID AND tr.active = 1
INNER JOIN Course c on c.courseID = se.courseID AND c.attendance = 1
INNER JOIN Calendar cal on cal.calendarID = c.calendarID AND cal.calendarID = tr.calendarID
LEFT JOIN BlendedLearningGroupAssignment blga on blga.personID = ro.personID AND (blga.endDate is null or blga.endDate >= getDate())
INNER JOIN Day d on d.calendarID = cal.calendarID AND convert(varchar, d.date, 101) = convert(varchar, getDate(), 101)
INNER JOIN blendedLearningGroupRemoteDay blgrd on blgrd.calendarID = d.calendarID AND blgrd.dayID = d.dayID AND blgrd.blendedLearningGroupID = blga.blendedLearningGroupID
LEFT JOIN studentParticipation sp on sp.sectionID = se.sectionID AND sp.personID = blga.personID AND convert(varchar, sp.date, 101) = convert(varchar, d.date, 101)Box B
AND (ro.endDate is null or ro.endDate >= getDate())
GROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumber
HAVING sum(cast(sp.participationConfirmation as int)) = 0 OR count(sp.participationConfirmation) = 0Student Queries for Academic Information
Students with Fewer Credits than Desired for Grade
This query returns students enrolled in a certain grade who have less than a specified amount of credits. This example returns all twelfth-graders who have fewer than 20 credits.
Box A
INNER JOIN v_TranscriptDetail td ON td.personID = student.personIDBox B
AND student.grade = '12' GROUP BY student.personID, student.lastname, student.firstname, student.grade, student.studentnumber
HAVING(SUM(td.creditsearned))< 20Students on the A Honor Roll
This query returns students who are considered "A" honor-roll students. This query may require user modifications to fit specific needs.
Box A
INNER JOIN TermSchedule ts on ts.structureID = student.structureIDINNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1INNER JOIN GradingTask k on k.name = 'Quarter Grade'LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1INNER JOIN GradingScore g on g.calendarID = student.calendarID and g.termID = t.termID and g.taskID = k.taskID and (g.score like 'A%')AND g.personID = student.personID LEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID and gx.termID = g.termID and gx.taskID = g.taskIDAND gx.personID = g.personID AND NOT (gx.score like 'A%')
Box B
AND gx.scoreID IS NULLStudents on the AB Honor Roll
This query returns students who are considered "A" or "B" honor-roll students. This query may require user modifications to fit specific needs.
Box A
INNER JOIN TermSchedule ts on ts.structureID = student.structureID INNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1 INNER JOIN GradingTask k on k.name = 'Nine Week'LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1INNER JOIN GradingScore g on g.calendarID = student.calendarID and g.termID = t.termID and g.taskID = k.taskID and (g.score like 'A%'OR g.score like 'B%') AND g.personID = student.personIDLEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID and gx.termID = g.termID and gx.taskID = g.taskID and gx.personID = g.personIDAND NOT (gx.score like 'A%'OR gx.score like 'B%')INNER JOIN GradingScore g3 ON g3.calendarID = g.calendarID AND g3.termID = g.termID AND g3.taskID = g.taskIDAND g3.personID = g.personID AND(g3.score LIKE 'B%')
Box B
AND gx.scoreID IS NULLStudents on the AB Honor Roll Excluding Students who only Received A Grades
This query returns students who are considered "A" or "B" honor-roll students, but excludes those who received only A's. This query may require user modifications to fit specific needs.
Box A
INNER JOIN TermSchedule ts on ts.structureID = student.structureID INNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1 INNER JOIN GradingTask k on k.name = 'Quarter' LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1INNER JOIN GradingScore g on g.calendarID = student.calendarIDAND g.termID = t.termID and g.taskID = k.taskIDAND (g.score like 'A%' OR g.score like 'B%') AND g.personID = student.personIDLEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarIDAND gx.termID = g.termID AND gx.taskID = g.taskIDAND gx.personID = g.personIDAND NOT (gx.score like 'A%' OR gx.score like 'B%') INNER JOIN GradingScore g3 ON g3.calendarID = g.calendarID AND g3.termID = g.termID AND g3.taskID = g.taskID AND g3.personID = g.personID AND(g3.score LIKE 'B%')
Box B
AND gx.personID IS NULLStudents who do not have an Assigned Academic Plan
This query returns students who do not have a Graduation Program or Career and Technical Education Program assigned to them.
Box A
LEFT JOIN programparticipation pp ON pp.personid = student.personid CROSS JOIN program p Box B
AND p.curriculum <> 1
AND pp.personid IS NULLStudent Queries for Behavior Information
Students without Behavior Events
This query returns students who do not have behavior event records in the selected calendar.
Box A
LEFT OUTER JOIN v_BehaviorDetail be ON student.personID = be.personID and student.calendarID = be.calendarIDBox B
AND BE.eventID IS NULLThis query returns students who do not have behavior event records in the selected calendar and date range.
Box A
LEFT OUTER JOIN v_BehaviorDetail bd ON bd.personID = student.personID AND bd.calendarID = student.calendarID AND bd.incidentDateBETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'
Box B
AND bd.eventID IS NULLStudents without Behavior Events and Students with Events where Role is Not Offender or Participant
This query returns students who do not have behavior events. It also returns students who have events in which they are not marked as an "offender" or as a "participant."
Box A
LEFT OUTER JOIN v_BehaviorDetail bd ON bd.personID = student.personID AND bd.calendarID = student.calendarIDBox B
AND bd.personID IS NULL OR (bd.role <> 'Offender'
AND bd.role <> 'Participant')Students without Behavior Resolutions
This query returns students who are involved in behavior events without resolutions.
Box A
INNER JOIN v_BehaviorDetail bd ON bd.personID = student.personID AND bd.calendarID = student.calendarIDBox B
AND bd.resolutionID IS NULLStudents with no Behavior Resolutions in Date Range
This query pulls students who do not have any behavior resolutions within the specified date range.
Box A
LEFT OUTER JOIN v_BehaviorDetail bd ON bd.personID = student.personID
AND bd.calendarID = student.calendarID AND bd.resolutionStartDate
BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'Box B
AND bd.resolutionID IS NULLParent and Guardian Related Queries
Students whose Parent/Guardians do not have Email Addresses
This query returns a list of students whose parents/guardians do not have an email address entered on Census Demographics.
Box A
JOIN relatedpair rp ON rp.personid1 = student.personid AND rp.guardian = 1 JOIN contact c ON c.personid = rp.personid2
Box B
GROUP BY student.personid, student.studentnumber,lastname, firstname, Grade
HAVING count(c.email)=0Students without Guardians
This query returns students who do not have a guardian relationship assigned.
Box A
JOIN RelatedPair rp ON rp.personid1 = student.personID
Box B
GROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumber HAVING COUNT(guardian)<1
Students with Multiple Guardians
This query returns students who have two or more guardian relationships assigned.
Box A
INNER JOIN RelatedPair rp ON rp.personID1 = student.personID
AND rp.guardian = 1Box B
GROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumber
HAVING COUNT(rp.personID1) > 2Students whose Parents/Guardians do not have a Portal Account
This query returns a list of students whose parents/guardian do not have a Campus Portal Account. If a parent/guardian is also a staff person, and only has a staff (Campus Application) account, the parent is not included in the results.
Box A
JOIN v_CensusContactSummary ccs ON ccs.PersonID = student.personID
LEFT JOIN UserAccount ua ON ua.personID = ccs.contactpersonidBox B
AND ua.userID IS NULL and ccs.guardian = 1 AND student.enddate IS NULLStudents whose Parents/Guardians do not have a Portal Account (Staff Inclusive Search)
This query returns students in the selected calendar(s) whose parents/guardians do not have a parent portal account. This includes students of staff members with a Campus Application/Campus Instruction account who do not have a parent portal account. Once a single guardian under the student's relationships has a portal account, no other guardians display on this list.(Note: Parent/guardian relationship is indicated by marking the Guardian and Portal checkboxes on the Relationships screen.)
Box A
JOIN relatedpair rp ON rp.personid1 = student.personid LEFT JOIN useraccount ua ON ua.personid = rp.personid2 AND ua.homepage IS NOT NULL AND ua.homepage <> 'nav-wrapper/TeacherApp/control-center/home'
Box B
AND rp.guardian = 1 AND rp.portal = 1 GROUP BY student.personid, student.studentnumber, student.firstname, student.lastname, student.grade HAVING COUNT(ua.personid) = 0
Students who do not have at least one parent or guardian with a portal account
This query returns students who do not have a parent/guardian who has a Campus Portal account.
Box A
JOIN relatedpair rp ON rp.personid1 = student.personid LEFT JOIN useraccount ua ON ua.personid = rp.personid2
Box B
AND rp.guardian = 1 AND rp.portal = 1 GROUP BY student.personid, student.studentnumber,student.firstname, student.lastname, student.grade HAVING COUNT(ua.personid) = 0
No guardian has a portal account where guardian and portal are both true on the relationship
This query returns students who have guardians but those guardians do not have portal accounts.
Box A
JOIN relatedpair rp ON rp.personid1 = student.personid
LEFT JOIN useraccount ua ON ua.personid = rp.personid2
Box B
AND rp.guardian = 1 AND rp.portal = 1
GROUP BY student.personid, student.studentnumber, student.firstname, student.lastname, student.grade
HAVING COUNT(ua.personid) = 0
No guardian has a portal account where guardian is true on the relationship
This query returns students who have guardians but those guardians do not have portal accounts; this query does not use the portal checkbox.
Box A
JOIN relatedpair rp ON rp.personid1 = student.personid
LEFT JOIN useraccount ua ON ua.personid = rp.personid2
Box B
AND rp.guardian = 1
GROUP BY student.personid, student.studentnumber, student.firstname, student.lastname, student.grade
HAVING COUNT(ua.personid) = 0
No guardian has a portal account where guardian is in the household and where guardian and portal are both true on the relationship
This query returns students who have guardians but those guardians do not have portal accounts, but the relationship record indicates portal.
Box A
LEFT JOIN (RelatedPair rp
JOIN HouseholdMember hmG ON hmG.personID = rp.personID2 And hmG.endDate IS NULL
JOIN HouseholdMember hmS ON hmS.personID = rp.personID1 And hmS.householdID = hmG.householdID
JOIN UserAccount ua ON ua.personID = rp.personID2) ON rp.personID1 = student.personID And rp.portal = 1 And rp.guardian = 1
Box B
AND (rp.personID1 IS NULL
Miscellaneous Student Queries
Finding Students Not Assigned Fees
This query returns all students who have no fees assigned.
Box A
LEFT JOIN feeassignment fa on student.personID = fa.personIDBox B
AND fa.personID is nullStudents with a Fee Balance for Any Year
This query returns students who owe fees for any school year.
Box A
JOIN v_FeeSummary fs on fs.personID = student.personIDBox B
AND fs.totalBalance <0List of Students who have a Free/Reduced Eligibility
This query returns a list of students who have a free or reduced eligibility.
Box A
INNER JOIN v_POSpatron p ON p.personID = student.personID
INNER JOIN v_POSEligibilityCurrent pe on pe.personID = p.personID
LEFT OUTER JOIN POSEligibility pos ON pos.personID = pe.personIDBox B
AND pos.eligibility IN ('R', 'F')List of Students who do NOT have a Free/Reduced Eligibility
This query returns a list of students who do not have a free or reduced eligibility.
Box A
LEFT OUTER JOIN POSEligibility pe ON pe.personid = student.personidBox B
AND pe.eligibility IS NULLFind a Student's Eligibility on a Certain Day
This query returns a student's eligibility on a certain day.
Box A
INNER JOIN v_POSpatron p ON p.personID = student.personID
INNER JOIN v_POSEligibilityCurrent pe on pe.personID = p.personIDBox B
AND p.type = 'student' AND (pe.elig_endDate <= GETDATE() OR pe.elig_endDate IS NULL) ORDER BY student.lastnameTo pull a specific date, change GETDATE to the desired date with single quotes (i.e., '05-23-2011')
Students without a Transportation Code
This query returns students who do not have a transportation code.
Box A
LEFT OUTER JOIN Transportation t ON t.personID = student.personID AND t.calendarID = student.calendarIDBox B
AND t.personID IS NULLStudents with Perfect Attendance
This query returns students who have perfect attendance records – that is, students who have no attendance marks.
Box A
LEFT OUTER JOIN attendance a ON a.personID = student.personID AND a.calendarID = student.calendarIDBox B
AND a.attendanceID IS NULLImpact Aid Employment
This query returns information on students whose parents are employed at Impact Aid sites.
Box A
INNER JOIN ImpactAidEmployment i on i.personID = student.personIDBox B
AND i.siteID is NOT NULL
AND student.endYear = 2010Students Assigned to a Particular Counselor
This query returns students who are assigned to a particular counselor. Change the lastName and firstName of the counselor in Box B.
Box A
INNER JOIN TeamMember tm ON tm.personID = student.personIDAND tm.module = 'counseling' AND tm.role = 'counselor' AND (tm.endDate IS NULL OR tm.endDate >= getDate())INNER JOIN individual i ON i.personID = tm.staffPersonID
Box B
AND i.lastName = 'Staff' AND i.firstName = 'Dean'Students whose Food Service Eligibility Code does not Match their Economic Indicator
This query returns students whose Food Service Eligibility status does not match the Economic Indicator assigned on their enrollment record.
In this example, Code 0 = Ineligible; 1 = Reduced Eligible; 2 = Free Eligible.
Box A
INNER JOIN Enrollment e ON e.enrollmentID = student.enrollmentID
AND (e.endDate IS NULL OR e.endDate >= getDate())
LEFT OUTER JOIN POSEligibility pos ON pos.personID = student.personID AND pos.endYear = student.endYearBox B
AND ((e.mealStatus = '0' AND pos.eligibility <> 'S')OR (e.mealStatus = '1' AND pos.eligibility <> 'R')OR (e.mealStatus = '2'AND pos.eligibility <> 'F')OR (e.mealStatus IS NULLAND pos.eligibility IS NOT NULL)OR (e.mealStatus IS NOT NULLAND pos.eligibility IS NULL))
Students Attending School Outside School Boundaries
This query returns information on students who attend a school located outside of the school boundary related to their address.
Box A
INNER JOIN School sc ON sc.schoolID = student.schoolIDINNER JOIN HouseholdMember hm on hm.personID = student.personIDAND (hm.secondary IS NULL OR hm.secondary = 0)AND hm.endDate IS NULLINNER JOIN HouseholdLocation hl ON hl.householdID = hm.householdIDAND (hl.secondary IS NULL or hl.secondary = 0)AND hl.endDate IS NULLINNER JOIN Address a ON a.addressID = hl.addressIDINNER JOIN SchoolBoundary sb ON sb.addressID = a.addressIDAND sb.schoolID <> student.schoolID
Box B
No text necessary
