## Departures from Origins and Arrivals at Destinations

 Author Message dwain.c SSCertifiable Group: General Forum Members Points: 6019 Visits: 6431 Comments posted to this topic are about the item Departures from Origins and Arrivals at Destinations My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSCertifiable Group: General Forum Members Points: 6019 Visits: 6431 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 timesWITH 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, EffectiveEndFROM Flights aINNER 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.htmLooking forward to hearing a discussion about this approach, in particular if anyone has a good general solution to the DST issue. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables mickyT Ten Centuries Group: General Forum Members Points: 1422 Visits: 3317 Hi DwainAfter reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.This allows the distance to be calculated using the Geography method STDistance and you can also start visualising the data`ALTER TABLE Airports ADD Location GeographyGOUPDATE Airports SET Location = Geography::STGeomFromText( 'POINT (' + CAST(CASE WHEN LongDir = 'E' THEN CAST(LongDeg AS DECIMAL(11,8)) ELSE CAST(-LongDeg AS DECIMAL(11,8)) END + CAST((LongMin / 60.00) + ( LongSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15)) + ' ' + CAST(CASE WHEN LatDir = 'N' THEN CAST(LatDeg AS DECIMAL(11,8)) ELSE CAST(-LatDeg AS DECIMAL(11,8)) END + CAST((LatMin / 60.00) + ( LatSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15)) + ')' ,4326 )WHERE LatDeg+LatMin+LatSec+LongDeg+LongMin+LongSec <> 0SELECT f.flight, o.IATA_Code, d.IATA_Code, f.SegmentDistanceMiles, f.SegmentDistanceKM, cast(o.Location.STDistance(d.Location) / 1000 as decimal(9,4)) CalcDistanceKM, case when o.location is not null and d.location is not null then Geography::STGeomFromText( 'LINESTRING (' + REPLACE(REPLACE(REPLACE(o.Location.ToString(),'POINT',''),'(',''),')','') + ', ' + REPLACE(REPLACE(REPLACE(d.Location.ToString(),'POINT',''),'(',''),')','') + ')' , 4326) else null end PathGeomFROM dailyflightschedules f inner join airports o on f.origin = o.IATA_Code inner join airports d on f.destination = d.IATA_Codefrom airports` dwain.c SSCertifiable Group: General Forum Members Points: 6019 Visits: 6431 mickyT (12/12/2012)Hi DwainAfter reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.This allows the distance to be calculated using the Geography method STDistance and you can also start visualising the data`ALTER TABLE Airports ADD Location GeographyGOUPDATE Airports SET Location = Geography::STGeomFromText( 'POINT (' + CAST(CASE WHEN LongDir = 'E' THEN CAST(LongDeg AS DECIMAL(11,8)) ELSE CAST(-LongDeg AS DECIMAL(11,8)) END + CAST((LongMin / 60.00) + ( LongSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15)) + ' ' + CAST(CASE WHEN LatDir = 'N' THEN CAST(LatDeg AS DECIMAL(11,8)) ELSE CAST(-LatDeg AS DECIMAL(11,8)) END + CAST((LatMin / 60.00) + ( LatSec / 60.00 / 60.00) AS DECIMAL (11,8)) AS varchar(15)) + ')' ,4326 )WHERE LatDeg+LatMin+LatSec+LongDeg+LongMin+LongSec <> 0SELECT f.flight, o.IATA_Code, d.IATA_Code, f.SegmentDistanceMiles, f.SegmentDistanceKM, cast(o.Location.STDistance(d.Location) / 1000 as decimal(9,4)) CalcDistanceKM, case when o.location is not null and d.location is not null then Geography::STGeomFromText( 'LINESTRING (' + REPLACE(REPLACE(REPLACE(o.Location.ToString(),'POINT',''),'(',''),')','') + ', ' + REPLACE(REPLACE(REPLACE(d.Location.ToString(),'POINT',''),'(',''),')','') + ')' , 4326) else null end PathGeomFROM dailyflightschedules f inner join airports o on f.origin = o.IATA_Code inner join airports d on f.destination = d.IATA_Codefrom airports`Pretty cool Micky!Do you need to run anything special on SQL 2008 to support the internal functions you're calling?Happy that someone actually read the article. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables mickyT Ten Centuries Group: General Forum Members Points: 1422 Visits: 3317 dwain.c (12/12/2012)[quote]mickyT (12/12/2012)Pretty cool Micky!Do you need to run anything special on SQL 2008 to support the internal functions you're calling?Happy that someone actually read the article.2008 comes with the geography and geometry as standard. Management Studio will also give you a reasonably simply map view of the data if you include a geography/geometry column. Just click on the spatial tab in the results pane.The trick is making sure that you pick the right SRID for the data. I used 4326 (WGS84). This is the same coordinate system used by GPS units. andrew.mckee SSC Journeyman Group: General Forum Members Points: 99 Visits: 245 FYI: Pacific flights crossing the date line can arrive the day before - eg:Air NewZealand NZ4082 Auckland to Tahiti(Papeete) Departs 3:50pm Sunday 16th Dec & arrives 9:50pm Saturday 15th Dec. dwain.c SSCertifiable Group: General Forum Members Points: 6019 Visits: 6431 andrew.mckee (12/12/2012)FYI: Pacific flights crossing the date line can arrive the day before - eg:Air NewZealand NZ4082 Auckland to Tahiti(Papeete) Departs 3:50pm Sunday 16th Dec & arrives 9:50pm Saturday 15th Dec.Andrew - Thanks for this. I guess I'm just going to have to run this through and see what modifications are needed t omake it work! My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dennisrg Valued Member Group: General Forum Members Points: 67 Visits: 32 If you want good data for airlines or airports go to http://transtats.bts.gov/Tables.asp?DB_ID=595&DB_Name=Aviation%20Support%20Tables&DB_Short_Name=Aviation%20Support%20Tables. dwain.c SSCertifiable Group: General Forum Members Points: 6019 Visits: 6431 dennisrg (12/23/2012)If you want good data for airlines or airports go to http://transtats.bts.gov/Tables.asp?DB_ID=595&DB_Name=Aviation%20Support%20Tables&DB_Short_Name=Aviation%20Support%20Tables.Dennis - Thanks. Actually I have a "paid for" table of airports that I couldn't post for obvious reasons. But I'll look into this as an alternate "free" option.Still swamped in holiday madness (and the PNG project that led to this article) so I've only had the time to offer short posts.Clickable version of the link provided by Dennis: http://transtats.bts.gov/Tables.asp?DB_ID=595&DB_Name=Aviation%20Support%20Tables&DB_Short_Name=Aviation%20Support%20Tables My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Neha05 SSC-Addicted Group: General Forum Members Points: 494 Visits: 60 useful article.