To Double Cursor, or Not to Double Cursor...

• Single flight - Good

• Two legs, there and back - Good

• Two legs, three different airport codes - Good

• Only one layover for any given trip - Good, BUT

• More than one layover for any given trip - This question, not handled yet.

DECLARE @origin1 varchar(50), @dest1 varchar(50), @origin2 varchar(50), @dest2 varchar(50), @leg int
SET @origin1 = (SELECT TOP 1 OriginApt FROM flights ORDER BY OriginDt Asc)
SET @dest1 = (SELECT TOP 1 DestApt FROM flights ORDER BY GapInMinutes Desc, DestDt Asc)
SET @leg = (SELECT TOP 1 LegNumber FROM flights ORDER BY GapInMinutes Desc, DestDt Asc)
SET @origin2 = (SELECT OriginApt FROM flights WHERE LegNumber = @leg + 1)
SET @dest2 = (SELECT TOP 1 DestApt FROM flights ORDER BY LegNumber Desc)
SELECT @origin1 As "Out Origin", @dest1 As "Out Destination", @origin2 AS "Return Origin", @dest2 AS "Return Destination"

Select allOpen in new window

Select f1.bookingid,f1.Origin, f2.Destination
From @flights f1
left join @flights f2 on f2.bookingId = f1.bookingid And f2.Gap>240
Where f1.LegNumber = 1

Select allOpen in new window

/*
drop table tab1 purge;
create table tab1(flightid number, legnumber number, originApt char(3), destApt char(3), gapInMinutes number);

insert into tab1 values(47,1,'A','C',80);
insert into tab1 values(47,2,'C','F',2518);
insert into tab1 values(47,3,'F','I',105);
insert into tab1 values(47,4,'I','J',230);
insert into tab1 values(47,5,'J','A',null);

insert into tab1 values(74,1,'A','C',60);
insert into tab1 values(74,2,'C','F',80);
insert into tab1 values(74,3,'F','I',105);
insert into tab1 values(74,4,'I','J',1447);
insert into tab1 values(74,5,'J','A',null);
commit;
*/

select flightid, max(origin) origin, max(dest) dest from (
select flightid, case when legnumber=1 then originapt end origin, case when gapInMinutes > 240 then destapt end dest
from tab1
)
group by flightid
/

Select allOpen in new window

DECLARE @KeyField1_parent varchar(17)
DECLARE @KeyField1 varchar(17) 
Declare @LegNumber int, @LegGroupNumber int, @GapInMinutes int
Declare @i int = 1, @NextParentLeg bit = 0
 
DECLARE cFlight CURSOR FOR   
SELECT DISTINCT l.KeyField1 
FROM temp.leg l
	LEFT JOIN temp.flight f ON l.KeyField1 = f.KeyField1
WHERE f.KeyField1 IS NULL		-- Has not already been accounted for in MatchingRule's 1-3
ORDER BY KeyField1
  
OPEN cFlight  
  
FETCH NEXT FROM cFlight INTO @KeyField1_parent
  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    --SELECT @KeyField1_parent
    
    DECLARE cLeg CURSOR FOR   
    SELECT KeyField1, LegNumber, LegGroupNumber, GapInMinutes 
    FROM temp.leg
    WHERE KeyField1 = @KeyField1_parent
    ORDER BY LegNumber
  
    --SELECT 'Outer' as label, KeyField1, LegNumber, LegGroupNumber, GapInMinutes 
    --FROM temp.leg
    --WHERE KeyField1 = @KeyField1_parent
    --ORDER BY LegNumber
    
    OPEN cLeg  
    FETCH NEXT FROM cLeg INTO @KeyField1, @LegNumber, @LegGroupNumber, @GapInMinutes  
  
	    WHILE (@@FETCH_STATUS = 0)
			begin
	   
			-- SELECT 'Inner' as label, @KeyField1, @LegNumber, @LegGroupNumber, @GapInMinutes  
	   
			IF @NextParentLeg = 1
				begin
				SET @i = @i + 1
				SET @NextParentLeg = 0
				end
				
			IF @GapInMinutes >= 240
				begin 
				SET @NextParentLeg = 1
				end
				
			UPDATE temp.leg 
			SET LegGroupNumber = @i 
			WHERE CURRENT OF cLeg
			
			FETCH NEXT FROM cLeg INTO @KeyField1, @LegNumber, @LegGroupNumber, @GapInMinutes
		  
			END
		
    CLOSE cLeg  
    DEALLOCATE cLeg  
    
    SELECT @i = 1, @NextParentLeg = 0
    
    -- Get the next flight. 
    FETCH NEXT FROM cFlight   
    INTO @KeyField1_parent
    
END   
CLOSE cFlight 
DEALLOCATE cFlight  

--SELECT * 
--FROM temp.leg 
--WHERE KeyField1 = @KeyField1_parent
--ORDEr BY KeyField1, LegNumber

-- 5 - More than two overall legs.  Per Thomas chat pick the first Origin and the Destination with the largest GapInMinutes.
INSERT INTO temp.flight (KeyField1, City1, City2, l.OriginDt, MatchingRule) 
SELECT f.KeyField1, minleg.OriginApt, maxleg.DestApt, maxleg.OriginDt, 5
FROM (SELECT DISTINCT KeyField1 FROM temp.leg WHERE LegGroupNumber > 2) f
	LEFT JOIN (
		SELECT KeyField1, OriginApt
		FROM temp.leg
		WHERE LegNumber = 1) minleg ON f.KeyField1 = minleg.KeyField1
	LEFT JOIN (
		SELECT l.KeyField1, l.DestApt, OriginDt
		FROM temp.leg l
			JOIN (SELECT KeyField1, MAX(GapInMinutes) as MaxGap FROM temp.leg GROUP BY KeyField1) maxgap 
				ON l.KeyField1 = maxgap.KeyField1 AND l.GapInMinutes = maxgap.MaxGap) maxleg ON f.KeyField1 = maxleg.KeyField1


INSERT INTO temp.flight_leg (KeyField1, City1, City2, l.OriginDt, MatchingRule) 
SELECT l.KeyField1, l.OriginApt, l.DestApt, l.OriginDt, f.MatchingRule
FROM temp.leg l 
	JOIN temp.flight f ON f.KeyField1 = l.KeyField1
WHERE f.MatchingRule = 5

-- 6 and 7:  Two overall legs, one or more layovers
IF OBJECT_ID('tempdb..temp.flight_with_layover') IS NOT NULL
	DROP TABLE temp.flight_with_layover

CREATE TABLE temp.flight_with_layover ( 
	KeyField1 varchar(17), 
	OriginApt1 char(3), 
	DestApt1 char(3),
	OriginApt2 char(3), 
	DestApt2 char(3),
	OriginDt1 datetime, 
	OriginDt2 datetime) 

-- When SQL Server us upgraded beyond 2008R2 recode this query using LEAD and LAG. 
;with scope as (
	SELECT DISTINCT l.KeyField1
	FROM temp.leg l
		LEFT JOIN temp.flight f ON l.KeyField1 = f.KeyField1
	WHERE f.KeyField1 IS NULL AND l.LegGroupNumber = 2),
MinMaxLegNumberOne as ( 
 	SELECT l.KeyField1, MIN(LegNumber) as MinLegNumberOne, MAX(LegNumber) as MaxLegNumberOne
	FROM temp.leg l
		JOIN scope s ON l.KeyField1 = s.KeyField1
	WHERE LegGroupNumber = 1
	GROUP BY l.KeyField1), 
MinMaxLegNumberTwo as ( 
 	SELECT l.KeyField1, MIN(LegNumber) as MinLegNumberTwo, MAX(LegNumber) as MaxLegNumberTwo
	FROM temp.leg l
		JOIN scope s ON l.KeyField1 = s.KeyField1
	WHERE LegGroupNumber = 2
	GROUP BY l.KeyField1), 
OriginAptOne as ( 
	SELECT l.KeyField1, l.OriginApt, l.OriginDt as OriginDt1 
	FROM temp.leg l 
		JOIN MinMaxLegNumberOne one ON l.KeyField1 = one.KeyField1 AND l.LegNumber = one.MinLegNumberOne), 
DestAptOne as ( 
	SELECT l.KeyField1, l.DestApt 
	FROM temp.leg l 
		JOIN MinMaxLegNumberOne one ON l.KeyField1 = one.KeyField1 AND l.LegNumber = one.MaxLegNumberOne), 
OriginAptTwo as ( 
	SELECT l.KeyField1, l.OriginApt, l.OriginDt as OriginDt2
	FROM temp.leg l 
		JOIN MinMaxLegNumberTwo one ON l.KeyField1 = one.KeyField1 AND l.LegNumber = one.MinLegNumberTwo), 
DestAptTwo as ( 
	SELECT l.KeyField1, l.DestApt 
	FROM temp.leg l 
		JOIN MinMaxLegNumberTwo one ON l.KeyField1 = one.KeyField1 AND l.LegNumber = one.MaxLegNumberTwo) 
INSERT INTO temp.flight_with_layover (KeyField1, OriginApt1, DestApt1, OriginApt2, DestApt2, OriginDt1, OriginDt2) 
SELECT DISTINCT 
	s.KeyField1,
	o1.OriginApt as OriginApt1, d1.DestApt as DestApt1, o2.OriginApt as OriginApt2, d2.DestApt as DestApt2, 
	o1.OriginDt1, o2.OriginDt2
FROM temp.leg l
	JOIN scope s ON l.KeyField1 = s.KeyField1
	JOIN OriginAptOne o1 ON l.KeyField1 = o1.KeyField1
	JOIN DestAptOne d1 ON l.KeyField1 = d1.KeyField1
	JOIN OriginAptTwo o2 ON l.KeyField1 = o2.KeyField1
	JOIN DestAptTwo d2 ON l.KeyField1 = d2.KeyField1

Select allOpen in new window