–Find the passengers which have spent the most time during flyings than others.
–Result set: passenger name, total flying time in minutes.

select top 1 with ties p.name, sum(case
when DATEDIFF(minute, time_out, time_in) >= 0 THEN DATEDIFF(minute, time_out, time_in)
else 1440 – (DatePart(hour, time_out) * 60 + DatePart(minute, time_out)) + (DatePart(hour, time_in) * 60 + DatePart(minute, time_in))
end) time from trip t
inner join Pass_in_trip pt on pt.trip_no = t.trip_no
inner join Passenger p on pt.ID_psg = p.ID_psg
group by p.ID_psg, p.name
order by time desc

## 2 thoughts on “Task 10 (aero)”

1. kiran says:

I’am getting error Incorrect syntax near ‘–’.

Please suggest me what to do for it

1. Try this one
/*Find the passengers which have spent the most time during flyings than others.
Result set: passenger name, total flying time in minutes.*/
select top 1 with ties p.name, sum(case
when DATEDIFF(minute, time_out, time_in) >= 0 THEN DATEDIFF(minute, time_out, time_in)
else 1440 – (DatePart(hour, time_out) * 60 + DatePart(minute, time_out)) + (DatePart(hour, time_in) * 60 + DatePart(minute, time_in))
end) time from trip t
inner join Pass_in_trip pt on pt.trip_no = t.trip_no
inner join Passenger p on pt.ID_psg = p.ID_psg
group by p.ID_psg, p.name
order by time desc