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 );
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 ,
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;
|