Wagner Portrait Group

WITH S AS (
SELECT P.personID,P.studentNumber AS studentNumber,I.firstName,I.lastName,E.grade
FROM Person P
JOIN Enrollment E ON P.personID = E.personID AND E.endDate IS NULL
JOIN Calendar Cal ON E.calendarID = Cal.calendarID AND Cal.summerSchool = 'false'
JOIN SchoolYear SY ON Cal.endYear = SY.endYear AND SY.active = 'true'
JOIN [IDENTITY] I ON P.currentIdentityID = I.identityID
),
RP AS (
SELECT RP.*,ROW_NUMBER() OVER (PARTITION BY RP.personID1 ORDER BY RP.personID2) AS guardianSeq
FROM S
LEFT JOIN RelatedPair RP ON RP.personID1 = S.personID AND RP.endDate IS NULL AND guardian = 'true'
),
HR AS (
SELECT SH.studentPersonID, SH.teacherDisplay
FROM v_StudentHomeroom SH
JOIN Trial T ON SH.trialID = T.trialID AND active = 1
JOIN SectionPlacement SecP ON SecP.sectionID = SH.sectionID AND SecP.trialID = SH.trialID
JOIN Term ON Term.termID = SecP.termID AND Term.startDate <= GETDATE() AND Term.endDate >= GETDATE()
)
SELECT DISTINCT
S.personID,
S.studentNumber,
S.firstName,
S.lastName,
S.grade,
C1.cellPhone AS guardian1Cell,
C1.email AS guardian1Email,
C2.cellPhone AS guardian2Cell,
C2.email AS guardian2Email,
PI.inputData AS lunchPin,
HR.teacherDisplay AS homeRoom
FROM S
LEFT JOIN RP RP1 ON S.personID = RP1.personID1 AND RP1.guardianSeq = '1'
LEFT JOIN RP RP2 ON S.personID = RP2.personID1 AND RP2.guardianSeq = '2'
LEFT JOIN Contact C1 ON RP1.PersonID2 = C1.personID
LEFT JOIN Contact C2 ON RP2.PersonID2 = C2.personID
LEFT JOIN POSIdentification PI ON PI.personID = S.personID AND PI.[TYPE] = 'PC'
LEFT JOIN HR ON HR.studentPersonID = S.personID
WHERE grade IN('06','07','08') AND HR.teacherDisplay IS NULL

SELECT DISTINCT P.personID,I.firstName,I.lastName,S.name AS buildingLocation,PI.inputData AS lunchPin
FROM EmploymentAssignment EA
JOIN Person P ON EA.personID = P.personID
JOIN School S ON EA.schoolID = S.schoolID
JOIN [IDENTITY] I ON P.currentIdentityID = I.identityID
LEFT JOIN POSIdentification PI ON PI.personID = P.personID
WHERE EA.endDate IS NULL