Task 10 (aero)

–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. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s