Task 7_LongestAndShortestTrips_21June2020

SELECT a.Id AS AccountId, CONCAT(a.FirstName, ' ', a.LastName) AS FullName, MAX(DATEDIFF(DAY, t.ArrivalDate, t.ReturnDate)) AS LongestTrip,
    MIN(DATEDIFF(DAY, t.ArrivalDate, t.ReturnDate)) AS ShortestTrip
        FROM Trips AS t
        JOIN AccountsTrips AS [at] ON t.Id = [at].TripId
        LEFT JOIN Accounts AS a ON [at].AccountId = a.Id
        WHERE a.MiddleName IS NULL AND t.CancelDate IS NULL
        GROUP BY a.Id, a.FirstName, a.LastName
        ORDER BY LongestTrip DESC,
                    ShortestTrip ASC