Differences

This shows you the differences between two versions of the page.

--- transportation:start [Sunday, August 13, 2023 11:13 PM] – Nathan C. McGuire
+++ transportation:start [Friday, April 05, 2024 12:08 PM] (current) – removed Nathan C. McGuire
@@ Line 1: / Line 1: @@
-====== Transportation ======
-[[https://routing.app-garden.com/osagemo#/|App Garden TransACT]]
-===== TransACT Students File =====
-<code sql>
-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
-</code>