App Garden TransACT

WITH A AS (
SELECT 
P.studentNumber,
I.lastName,
I.middleName,
I.firstName,
I.raceEthnicity,
I.gender,
I.birthdate,
S.name AS schoolName,
S.number AS schoolNumber,
E.grade,
CASE WHEN PA.number IS NULL THEN '' ELSE PA.number END + 
CASE WHEN PA.prefix IS NULL THEN '' ELSE ' ' + PA.prefix END + 
CASE WHEN PA.street IS NULL THEN '' ELSE ' ' + PA.street END + 
CASE WHEN PA.tag IS NULL THEN '' ELSE ' ' + PA.tag END + 
CASE WHEN PA.dir IS NULL THEN '' ELSE ' ' + PA.dir END + 
CASE WHEN PA.apt IS NULL THEN '' ELSE ' ' + PA.apt END AS [address],
PA.city,
PA.[state],
PA.zip,
PA.longitude,
PA.latitude,
CASE WHEN MA.number IS NULL THEN '' ELSE MA.number END + 
CASE WHEN MA.prefix IS NULL THEN '' ELSE ' ' + MA.prefix END + 
CASE WHEN MA.street IS NULL THEN '' ELSE ' ' + MA.street END + 
CASE WHEN MA.tag IS NULL THEN '' ELSE ' ' + MA.tag END + 
CASE WHEN MA.dir IS NULL THEN '' ELSE ' ' + MA.dir END + 
CASE WHEN MA.apt IS NULL THEN '' ELSE ' ' + MA.apt END AS mailingAddress,
MA.city AS mailingCity,
MA.[state] AS mailingState,
MA.zip AS mailingZip,
CI1.lastname + ', ' + CI1.firstname AS contact1FullName,
C1.homePhone AS contact1HomePhone,
C1.cellPhone AS contact1CellPhone,
C1.workPhone AS contact1WorkPhone,
C1.email AS contact1Email,
RP1.name AS contact1Relationship,
CI2.lastname + ', ' + CI2.firstname AS contact2FullName,
C2.homePhone AS contact2HomePhone,
C2.cellPhone AS contact2CellPhone,
C2.workPhone AS contact2WorkPhone,
C2.email AS contact2Email,
RP2.name AS contact2Relationship,
CI3.lastname + ', ' + CI3.firstname AS contact3FullName,
C3.homePhone AS contact3HomePhone,
C3.cellPhone AS contact3CellPhone,
C3.workPhone AS contact3WorkPhone,
C3.email AS contact3Email,
RP3.name AS contact3Relationship,
E.endDate AS withdrawnDate,
TC.value AS comments,
CASE WHEN IEP.startDate IS NOT NULL AND IEP.endDate IS NULL THEN 'Y' END AS IEP,
TDP.value AS districtProvided,
TCR.value AS changeRequested,
H.householdID,
T.inBus,
T.inTime,
T.outBus,
T.outTime
FROM Person P
JOIN Enrollment E ON P.personID = E.personID
JOIN Calendar C ON E.calendarID = C.calendarID
JOIN SchoolYear SY ON SY.endYear = C.endYear
JOIN [IDENTITY] I ON I.identityID = P.currentIdentityID 
JOIN School S ON S.schoolID = C.schoolID
LEFT JOIN HouseholdMember HM ON HM.personID = P.personID AND HM.secondary = 0 AND HM.endDate IS NULL
LEFT JOIN Household H ON H.householdID = HM.householdID
LEFT JOIN HouseholdLocation HLP ON HLP.householdID = H.householdID AND HLP.physical = 1 AND HLP.endDate IS NULL
LEFT JOIN [Address] PA ON PA.addressID = HLP.addressID
LEFT JOIN HouseholdLocation HLM ON HLM.householdID = H.householdID AND HLM.mailing = 1 AND HLM.endDate IS NULL
LEFT JOIN [Address] MA ON MA.addressID = HLM.addressID
LEFT JOIN RelatedPair RP1 ON RP1.personID1 = P.personID AND RP1.seq = 1
LEFT JOIN RelatedPair RP2 ON RP2.personID1 = P.personID AND RP2.seq = 2
LEFT JOIN RelatedPair RP3 ON RP3.personID1 = P.personID AND RP3.seq = 3
LEFT JOIN Person CP1 ON CP1.personID = RP1.personID2
LEFT JOIN Person CP2 ON CP2.personID = RP2.personID2
LEFT JOIN Person CP3 ON CP3.personID = RP3.personID2
LEFT JOIN [IDENTITY] CI1 ON CI1.identityID = CP1.currentIdentityID
LEFT JOIN [IDENTITY] CI2 ON CI2.identityID = CP2.currentIdentityID
LEFT JOIN [IDENTITY] CI3 ON CI3.identityID = CP3.currentIdentityID
LEFT JOIN Contact C1 ON CP1.personID = C1.personID
LEFT JOIN Contact C2 ON CP2.personID = C2.personID
LEFT JOIN Contact C3 ON CP3.personID = C3.personID
LEFT JOIN Transportation T ON T.personID = P.personID AND C.calendarID = T.calendarID AND T.endDate IS NULL
LEFT JOIN CustomTransportation TC ON TC.transportationID = T.transportationID AND TC.attributeID = 1066 --Transportation Comment
LEFT JOIN ProgramParticipation IEP ON IEP.personID = P.personID AND IEP.programID = 13 --IEP
LEFT JOIN CustomStudent TDP ON TDP.personID = P.personID AND TDP.attributeID = 1077 --District Provided Transportation Requested
LEFT JOIN CustomStudent TCR ON TCR.personID = P.personID AND TCR.attributeID = 1078 --Changes to District Provided Transportation Since Last Year
WHERE C.summerSchool = 0
AND SY.active = 1
)
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY studentNumber ORDER BY Contact1FullName) AS ROW
    FROM A
) AS B
WHERE B.row = 1
ORDER BY lastName,firstName