CREATE TABLE #Booking (
book_key  char(5),
departure_date  smalldatetime
)
go

CREATE TABLE #Itinerary (
ItineraryKey int Identity PRIMARY KEY NONCLUSTERED ,
book_key  char(5),
carrier   char(2),
Itinerary_type  int,
line_order_number int
)
go

CREATE TABLE #Hold_ticket (
book_key  char(5)
)
go

INSERT INTO #Booking VALUES( '31C4F' , '05OCT00' );
INSERT INTO #Booking VALUES( '31C4G' , '07OCT00' );
INSERT INTO #Hold_ticket VALUES( '31C4F' );
INSERT INTO #Hold_ticket VALUES( '31C4G' );
INSERT INTO #Itinerary VALUES( '31C4F' , 'GO' , 8 , 1 );
INSERT INTO #Itinerary VALUES( '31C4F' , 'AA' , 7 , 2 );
INSERT INTO #Itinerary VALUES( '31C4F' , 'UA' , 7 , 3 );
INSERT INTO #Itinerary VALUES( '31C4F' , 'AA' , 7 , 4 );
INSERT INTO #Itinerary VALUES( '31C4G' , 'FF' , 7 , 1 );
INSERT INTO #Itinerary VALUES( '31C4F' , 'GO' , 9 , 2 );
INSERT INTO #Itinerary VALUES( '31C4G' , 'AA' , 7 , 3 );
go

-- All the itineraries
SELECT i.carrier, i.itinerary_type, i.line_order_number, i.book_key
FROM #Hold_ticket AS h
 JOIN #Booking AS b
        ON h.book_key = b.book_key
 JOIN #Itinerary AS i
        ON h.book_key = i.book_key
WHERE b.departure_date Between '01oct00' And '10oct00'
ORDER BY b.book_key;
/*
carrier itinerary_type line_order_number book_key 
------- -------------- ----------------- -------- 
GO                   8                 1 31C4F
AA                   7                 2 31C4F
UA                   7                 3 31C4F
AA                   7                 4 31C4F
GO                   9                 2 31C4F
FF                   7                 1 31C4G
AA                   7                 3 31C4G
*/
GO

-- Objective:
-- For each booking, we need to get the itinerary with the
-- minimum itinerary type and order_number. So from the above
-- result set , we need the rows:
/*
carrier itinerary_type line_order_number book_key 
------- -------------- ----------------- -------- 
AA                   7                 2 31C4F
FF                   7                 1 31C4G
*/
/*
        The three SELECT statements below do the same, but the 2nd SELECT 
        with EXISTS will be less costly in terms of I/O. It will also be
        as fast as or faster than the SELECT with JOIN. The difference will
        be when you add more rows to the tables. The 1st SELECT statement is
        the SQL-92 version of the query & can be used in SQL6x.
*/
-- SQL6x version & ANSI SQL-92 version
SELECT i.carrier , i.Itinerary_type, i.line_order_number, i.book_key
FROM #Itinerary AS i
WHERE EXISTS( SELECT *
              FROM #Hold_ticket AS h
              JOIN #Booking AS b
                      ON h.book_key = b.book_key
              WHERE  b.departure_date Between '01oct00' And '10oct00' And
                     h.book_key = i.book_key
       ) And
       RIGHT( REPLICATE( '0' , 10 ) + CONVERT( varchar , i.Itinerary_Type ) , 8 )
        + RIGHT( REPLICATE( '0' , 10 ) + CONVERT( varchar , i.line_order_number ) , 8 )
       = ( SELECT MIN( RIGHT( REPLICATE( '0' , 10 ) + CONVERT( varchar , i1.Itinerary_Type ) , 8 )
                        + RIGHT( REPLICATE( '0' , 10 ) + CONVERT( varchar , i1.line_order_number ) , 8 ) )
           FROM #Itinerary AS i1
           WHERE i1.book_key = i.book_key
       );

SELECT i.carrier , i.Itinerary_type, i.line_order_number, i.book_key
FROM #Itinerary AS i
WHERE EXISTS( SELECT *
              FROM #Hold_ticket AS h
              JOIN #Booking AS b
                      ON h.book_key = b.book_key
              WHERE  b.departure_date Between '01oct00' And '10oct00' And
                     h.book_key = i.book_key
       ) And
       i.ItineraryKey IN ( SELECT TOP 1 i1.ItineraryKey
                 FROM #Itinerary AS i1
                 WHERE i1.book_key = i.book_key
                 ORDER BY i1.Itinerary_type , i1.line_order_number
       );

SELECT i.carrier, i.itinerary_type, i.line_order_number, i.book_key
FROM #Hold_ticket AS h
 JOIN #Booking AS b
        ON h.book_key = b.book_key
 join #Itinerary AS i
        ON h.book_key = i.book_key
WHERE b.departure_date Between '01oct00' And '10oct00' And
       i.ItineraryKey IN ( SELECT TOP 1 i1.ItineraryKey
                 FROM #Itinerary AS i1
                 WHERE i1.book_key = i.book_key
                 ORDER BY i1.Itinerary_type , i1.line_order_number
       );
GO
DROP TABLE #Booking;
DROP TABLE #Hold_Ticket;
DROP TABLE #Itinerary;

This page was last updated on May 01, 2006 04:28 PM.