Complex function
-
I need some help with a function that will string together flight itinerary details from my Itinerary table per ticket stored in my Ticket table in my db - there is a one to many relationship as a single ticket can have multiple itinerary rows. The Itinerary table that hosts the From and To fields that makeup the itinerary string also foreign key on to the Ticket table (TicketID and BookingNo) - i.e. for a return flight from London Heathrow to LA the Itinerary table would look like this
BookingNo TicketID From To
12345 1 LHR LAX
12345 1 LAX LHR
So far so good, but the tricky part is yet to come...
The string needs to be formated so that it drops the From field if the From field matches the previous To field for that ticket, so it should look like this:
"LHR LAX LHR" and not "LHR LAX LAX LHR" (notice that the LAX From field does not appear in the first example)
HELP!!!
Let me know if you need any more info - Thanks!
-
Choose Bagdad instead of LA as the destination and you won't have to worry about the return flight.
Did that help?
_/_/_/ paramind _/_/_/ -
How about a CASE statement?
-
Now to the database part of the question ...
One simple way to achieve this is a simple pattern replacement: since all airports have 3-char-shortnames and airport shortnames are unique, any occurrence of airport [blank] airport has to be replaced by: airport.
If all your flights match the above pattern (that is: last arrival is always next departuer), it's even easier:
declare @intinerary varchar(8000)
select
@intinerary = @intinerary + [from] + ' '
from flight
where
bookingnumber = 1
AND flightindex <> (Select MAX(flightindex) from flight)
order by flightindex
SET @intinerary = @intinerary + (select [to]
FROM flight
WHERE bookingnumber = @bookingnumber
and flightindex = (Select MAX(flightindex) from flight)
Return @itinerary
PS: you must have some sort of flightindex because otherwise no one can tell the order of the flights)
_/_/_/ paramind _/_/_/ -
How you string suppose to look if you 1st flight to JFK airport in NY and 2nd flight from another airport?
Should you display taxi travel?
_____________
Code for TallyGenerator -
Hi James,
Is something like this what you're looking for?
--This SQL script is safe to run
DECLARE @Itinerary TABLE (BookingNo INT, TicketID INT, FromId CHAR(3), ToId CHAR(3), ItineraryNumber INT)
INSERT INTO @Itinerary
SELECT 12345, 1, 'LHR', 'LAX', 1
UNION SELECT 12345, 1, 'LAX', 'LHR', 2
DECLARE @ItineraryString VARCHAR(8000)
SET @ItineraryString = ''
SELECT
@ItineraryString =
CASE
WHEN FromId = RIGHT(@ItineraryString, 3) THEN @ItineraryString + ' ' + ToId
ELSE @ItineraryString + ' ' + FromId + ' ' + ToId
END
FROM @Itinerary
WHERE BookingNo = 12345 AND TicketID = 1
ORDER BY ItineraryNumber
SELECT LTRIM(@ItineraryString)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Notwithstanding the above
CREATE FUNCTION dbo.udf_itinerary (@TicketID int, @BookingNo int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @itinerary varchar(8000)
SET @itinerary = ''
SELECT @itinerary = @itinerary + i.[From] + ' '
FROM [Itinerary] i
INNER JOIN [Itinerary] i2
ON i2.TicketID=i.TicketID
AND i2.BookingNo=i.BookingNo
AND i2.[From]=i.[To]
ORDER BY i.ItineraryID ASC
RETURN @itinerary
END
SELECT TicketID, BookingNo,
dbo.udf_itinerary (TicketID, BookingNo)
FROM [Ticket]
This requires a column in the Itinerary table (ItineraryID in my example) to order the results otherwise the order of the airports will be arbitrary
Also this will only show multiple hops
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks guys - i will give some of these a shot and let you know!
-
Thanks Ryan! I managed to use your template and apply it to my database with some tweaking to build in stop-overs etc.
Do you know if this sort of function can be tweaked to work in Sybase - i think i will have to create a Sybase equivalent using cursors!
Thanks again - and to all you guys who also contributed, much appreciated!
-
No problem James - solving problems like this is fun!
I don't know anything about Sybase though - sorry
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply