Transportation

Import Transportation Comment

To run this query a table named just.transportation_comments must be created by file import.

INSERT INTO CustomTransportation
SELECT ca.attributeID,snh.personID,tc.TrnsCmt AS VALUE,GETDATE() AS DATE,t.transportationID
FROM cust.transportation_comments tc
LEFT JOIN StudentNumberHistory snh ON snh.active = 1 AND tc.id = snh.studentNumber
CROSS JOIN
(
    SELECT attributeID
    FROM CampusAttribute
    WHERE object = 'Transportation' AND element = 'TransportationComment'
    ) ca
JOIN Transportation t ON t.PersonID = snh.personID
JOIN Calendar c ON t.calendarID = c.calendarID
JOIN SchoolYear sy ON sy.active = 1 AND sy.endYear = c.endYear