Between submission of this article and publication, the POC for PNG has been tweaked a little and while doing so I noticed a couple of issues so I will publish this here as errata:

1. In creatng the DailyFlightSchedules table, the segment distance in miles is INT, so it is likely you'd want the segment distance in KM to be likewise an INT. CAST the computed column's results as follows to achieve this.

CREATE TABLE DailyFlightSchedules

(SegmentID INT IDENTITY

,Airline CHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

FOREIGN KEY REFERENCES Airlines(AirlineCode)

,Flight VARCHAR(4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,Origin CHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

FOREIGN KEY REFERENCES Airports(IATA_Code)

,Destination CHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

FOREIGN KEY REFERENCES Airports(IATA_Code)

,DepartureTime TIME NOT NULL

,ArrivalTime TIME NOT NULL

,EffectiveStart DATETIME NULL

,EffectiveEnd DATETIME NULL

,[Day] CHAR(2) NULL

,[DayOfWeek] INT DEFAULT (127) -- Default = every day of the week

,SegmentTime INT DEFAULT(0)

,SegmentDistanceMiles INT NULL DEFAULT(0)

,SegmentDistanceKM AS (CAST(ROUND(SegmentDistanceMiles * 1.60934, 0) AS INT))

,FlightNo AS (Airline+Flight) PERSISTED

,DepartureDay AS (CASE LEFT([Day], 1) WHEN '1' THEN '+1' ELSE '' END)

,ArrivalDay AS (CASE RIGHT([Day], 1) WHEN '1' THEN '+1' ELSE '' END))

2. An issue cropped up while I was experimenting with effective dates while at the same time only displaying arrival and departure times as TIME. Basically the function wasn't recognizing segments with different effective dates as different flights. This version of the FlightSchedules iTVF should correct this and also now returns effective dates of the flight.

ALTER FUNCTION [dbo].[FlightSchedule]

(@Airline CHAR(2) = NULL

,@Origin VARCHAR(3) = NULL

,@Destination VARCHAR(3) = NULL

,@DepartureDate DATETIME = NULL)

RETURNS TABLE

RETURN

-- Select all flight segments with corresponding departure and arrival times

WITH Flights AS (

SELECT SegmentID, FlightNo, Origin, Destination

,DepartureTime

,ArrivalTime

,[Day]

-- Format for display

,[DaysOfWeek]=CASE [DayOfWeek] % 2 WHEN 1 THEN 'SU' ELSE '^^' END +

CASE ([DayOfWeek]/2) % 2 WHEN 1 THEN 'MO' ELSE '^^' END +

CASE ([DayOfWeek]/4) % 2 WHEN 1 THEN 'TU' ELSE '^^' END +

CASE ([DayOfWeek]/8) % 2 WHEN 1 THEN 'WE' ELSE '^^' END +

CASE ([DayOfWeek]/16) % 2 WHEN 1 THEN 'TH' ELSE '^^' END +

CASE ([DayOfWeek]/32) % 2 WHEN 1 THEN 'FR' ELSE '^^' END +

CASE ([DayOfWeek]/64) % 2 WHEN 1 THEN 'SA' ELSE '^^' END

,[DayOfWeek]

,DepartureDay, ArrivalDay

-- The PARTITION below identifies cases where the same flight number

-- operates on a different schedule on different days of the week

,SegmentNo=ROW_NUMBER() OVER (

PARTITION BY FlightNo, [DayOfWeek] ORDER BY SegmentID)

,SegmentTime

,SegmentDistanceMiles

,SegmentDistanceKM

,OperatingDate

,Airline

,EffectiveStart, EffectiveEnd

FROM DailyFlightSchedules

-- Empty string as @DepartureDate is handled as a NULL (no) value

-- Drop the time portion of any actual date supplied.

OUTER APPLY (

SELECT ISNULL(DATEADD(day, 0, DATEDIFF(day, 0, @DepartureDate)), '')

) b(OperatingDate)

WHERE (OperatingDate = '' OR EffectiveStart IS NULL OR

OperatingDate >= EffectiveStart) AND

(OperatingDate = '' OR EffectiveEnd IS NULL OR

OperatingDate <= EffectiveEnd))

SELECT a.FlightNo, SegmentNo, a.Origin, a.Destination

,DepartureTime=CASE

WHEN OperatingDate = ''

THEN CONVERT(VARCHAR(5), a.DepartureTime, 108)

WHEN OperatingDate <> '' AND InitialDeparture > 1 AND

a.SegmentNo > 1 AND [Day] = '11'

THEN CONVERT(VARCHAR(10), OperatingDate, 121) + ' ' +

CAST(a.ArrivalTime AS VARCHAR(5))

ELSE CONVERT(VARCHAR(16)

,DATEADD(day, CAST(LEFT([Day], 1) AS INT), OperatingDate) + ' ' +

CAST(a.DepartureTime AS VARCHAR(5)),121) END

,ArrivalTime=CASE

WHEN OperatingDate = ''

THEN CONVERT(VARCHAR(5), a.ArrivalTime, 108)

WHEN OperatingDate <> '' AND InitialDeparture > 1 AND

a.SegmentNo > 1 AND [Day] = '11'

THEN CONVERT(VARCHAR(10), OperatingDate, 121) + ' ' +

CAST(a.ArrivalTime AS VARCHAR(5))

ELSE CONVERT(VARCHAR(16)

,DATEADD(day, CAST(RIGHT([Day], 1) AS INT), OperatingDate) + ' ' +

CAST(a.ArrivalTime AS VARCHAR(5)),121) END

,DepartureDay=CASE WHEN OperatingDate IS NULL THEN DepartureDay ELSE ' ' END

,ArrivalDay=CASE WHEN OperatingDate IS NULL THEN ArrivalDay ELSE ' ' END

-- Decode the DayOfWeek INT to a string of operating days

,DaysOfWeek

,SegmentTime

,SegmentDistanceMiles

,SegmentDistanceKM

,EffectiveStart, EffectiveEnd

FROM Flights a

INNER JOIN (

SELECT FlightNo, [DayOfWeek]

,InitialDeparture=CASE

-- No (NULL) or empty string as origin supplied so include

-- the earliest segment

WHEN ISNULL(@Origin, '') = '' THEN MIN(SegmentNo)

-- If % not in @Origin, we find the first segment that's an exact match

-- to @Origin

WHEN CHARINDEX('%', @Origin) = 0

THEN MIN(CASE WHEN Origin = @Origin THEN SegmentNo END)

-- If % in @Origin, do a LIKE search to identify the first matching segment

ELSE MIN(CASE WHEN Origin LIKE @Origin THEN SegmentNo END) END

,FinalArrival=CASE

-- No (NULL) or empty string as destination supplied so include

-- the earliest segment

WHEN ISNULL(@Destination, '') = '' THEN MAX(SegmentNo)

-- If % not in @Destination, we find the last segment that's an exact match

-- to @Destination

WHEN CHARINDEX('%', @Destination) = 0

THEN MAX(CASE WHEN Destination = @Destination THEN SegmentNo END)

-- If % in @Destination, do a LIKE search to identify the last matching segment

ELSE MAX(CASE WHEN Destination LIKE @Destination THEN SegmentNo END) END

FROM Flights

GROUP BY FlightNo, [DayOfWeek], EffectiveStart) b

ON a.FlightNo = b.FlightNo AND a.[DayOfWeek] = b.[DayOfWeek]

WHERE -- Select only segments between the search criteria

a.SegmentNo BETWEEN InitialDeparture AND FinalArrival AND

-- Empty string as @Airline is handled as a NULL (no) value

(ISNULL(@Airline, '') = '' OR Airline = @Airline) AND

-- OperatingDate IS NULL if no @DepartureDate was supplied

(OperatingDate = '' OR

-- Decode the days of the week on which the flight operates

-- Note: Sensitive to @@DATEFIRST

(b.[DayOfWeek]/POWER(2, DATEPART(dw, OperatingDate) -1)) % 2 = 1)

3. My apologies that the link to the map of PNG doesn't seem to display the graphic properly in the article. PNG is really an interesting place so the article is woefully incomplete without a colorful map such as the one you can find here: http://www.oceania-maps.com/guinea.htm

Looking forward to hearing a discussion about this approach, in particular if anyone has a good general solution to the DST issue.