SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Departures from Origins and Arrivals at Destinations


Departures from Origins and Arrivals at Destinations

Author
Message
dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 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
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 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 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.


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
mickyT
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4130 Visits: 3320
Hi Dwain

After 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 Geography
GO

UPDATE 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 <> 0

SELECT 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 PathGeom
FROM dailyflightschedules f
inner join airports o on f.origin = o.IATA_Code
inner join airports d on f.destination = d.IATA_Code
from airports


dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 Visits: 6431
mickyT (12/12/2012)
Hi Dwain

After 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 Geography
GO

UPDATE 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 <> 0

SELECT 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 PathGeom
FROM dailyflightschedules f
inner join airports o on f.origin = o.IATA_Code
inner join airports d on f.destination = d.IATA_Code
from 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
mickyT
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4130 Visits: 3320
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
andrew.mckee
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 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
dennisrg
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 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
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28493 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
Neha05
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 60
useful article.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search