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.