CREATE TABLE #Flights ( 
seriesId int , fltid  int , 
fltDate  datetime , aircraftid  int ,
outschedId  int , returnschedid int
);

set dateformat dmy;
insert #Flights values(  1 , 1 , '01-08-2000' , 4 , 10 , 11 );
insert #Flights values(  1 , 2 , '08-08-2000' , 4 , 10 , 11 );
insert #Flights values(  1 , 3 , '15-08-2000' , 4 , 15 , 20 );
insert #Flights values(  1 , 4 , '22-08-2000' , 4 , 10 , 11 );
insert #Flights values(  1 , 5 , '29-08-2000' , 8 , 10 , 11 );
insert #Flights values(  1 , 6 , '05-09-2000' , 8 , 10 , 11 );
insert #Flights values(  1 , 7 , '12-09-2000' , 8 , 10 , 11 );

/* Give a table of flights and their info.

seriesId fltid  fltDate  aircraftid  outschedId  returnschedid
  1        1     01-08-2000    4          10            11
  1        2     08-08-2000    4          10            11
  1        3     15-08-2000    4          15            20
  1        4     22-08-2000    4          10            11
  1        5     29-08-2000    8          10            11
  1        6     05-09-2000    8          10            11
  1        7     12-09-2000    8          10            11

-- Required output of the schedule
seriesId startDate   endDate     fltCount aircraft  outsched Retsched
  1      01-08-2000  08-08-2000     2        4        10       11
  1      15-08-2000  15-08-2000     1        4        15       20
  1      22-08-2000  22-08-2000     1        4        10       11
  1      29-08-2000  12-09-2000     3        8        10       11
*/

SELECT  seriesid ,
        MIN( fltdate ) AS startdate ,
        MAX( fltdate ) AS enddate ,
        COUNT( fltid ) AS fltcount ,
        aircraftid , outschedid , returnschedid
FROM (
SELECT seriesid , aircraftid , outschedid , returnschedid , fltdate , fltid ,
        -- Check for any flight that belongs to same series + airlines +
        -- departure + arrival schedule. We should do this only for all
        -- flights except the last one that doesn't have any other flight after it.
        -- Once we setup this sequence column, the rest is easy with a GROUP BY &
        -- MIN / MAX functions.
        CASE WHEN NOT EXISTS( SELECT * FROM #Flights AS t2
                              WHERE t1.seriesid = t2.seriesid And
                                    t1.aircraftid = t2.aircraftid And
                                    t1.outschedid = t2.outschedid And
                                    t1.returnschedid = t2.returnschedid And
                                    t2.fltdate > t1.fltdate ) And
                        t1.fltid < ( SELECT MAX( t3.fltid ) FROM #Flights AS t3 )
                THEN 1 
                ELSE 0 
        END  AS SameSeq
FROM #Flights AS t1
) AS t
GROUP BY seriesid , aircraftid , outschedid , returnschedid , SameSeq
ORDER BY seriesid , startdate , enddate , aircraftid , outschedid , returnschedid;
-- Output of derived table alone to see how the sequence of the flights are determined:
/*
seriesid aircraftid outschedid returnschedid fltdate                 fltid SameSeq     
-------- ---------- ---------- ------------- ----------------------- ----- -------
       1          4         10            11 2000-08-01 00:00:00.000     1       0 
       1          4         10            11 2000-08-08 00:00:00.000     2       0 
       1          4         15            20 2000-08-15 00:00:00.000     3       1 
       1          4         10            11 2000-08-22 00:00:00.000     4       1 
       1          8         10            11 2000-08-29 00:00:00.000     5       0 
*/

-- Final expected output:
/*
seriesid startdate               enddate                 fltcount aircraftid outschedid returnschedid 
-------- ----------------------- ----------------------- -------- ---------- ---------- ------------- 
       1 2000-08-01 00:00:00.000 2000-08-08 00:00:00.000        2          4         10            11 
       1 2000-08-15 00:00:00.000 2000-08-15 00:00:00.000        1          4         15            20 
       1 2000-08-22 00:00:00.000 2000-08-22 00:00:00.000        1          4         10            11 
       1 2000-08-29 00:00:00.000 2000-09-12 00:00:00.000        3          8         10            11 
*/
This page was last updated on May 01, 2006 04:28 PM.