SELECT t.flno,
       MIN(t.START)                        AS NewStart,
       MAX(t.END)                          AS NewEnd,
       t.orig,
       t.dest,
       t.overnight,
       t.class,
       t.route_code,
       MAX(t.d1) + MAX(t.d2) + MAX(t.d3) + MAX(t.d4)
       + MAX(t.d5) + MAX(t.d6) + MAX(t.d7) AS Pattern,
       (SELECT TOP 1 ac_name
        FROM   flightlist AS t2
        WHERE  t2.flno = t.flno
        GROUP  BY ac_name
        ORDER  BY COUNT(*) DESC,
                  ac_name)                 AS ac_name
FROM   flightlist t
GROUP  BY t.flno,
          t.orig,
          t.dest,
          t.overnight,
          t.class,
          t.route_code;