Row Comparisons - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Row Comparisons

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2011-12-15 : 14:30:30

I created a table which displays flights with the following columns: TripNumber, AccountName, Faretype, DepartureState, ArrivalState, DepartureDate, DepartureTime, DepartureAirportCode, ArrivalAirportCode, and ArrivalTime. I used a where statement to filter the results to display only:

1. Flights where the DepartureState is in network and ArrivalState is out of network

OR

2. Flights where the ArrivalState is in network and the DepartureState is out of network

I need to filter the report further to only display flights where:

1. Within 48 hours of an arrival there are no departures from the same state

2. Within 48 hours of a departure there are no arrivals into the same state

Any ideas how I can do this?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-15 : 15:15:40

you need to tell us what some of the predicates mean?

Likw what is this?

DepartureState is in network and ArrivalState is out of network

I would suggest you post DDL, sample data in DML Form and what the expected results will look like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2011-12-15 : 15:49:36

Thanks for replying. Our network is defined by state. States CA, NV, AZ, TX, LA, OK are considered "in network". Basically I have a list of passenger occurpied flights that are arriving or departing from states that are out of network.

I need to filter them further to exclude arrival/departures to and from the same state within 48 hours.

For example: If a flight arrives CO (Colorado) on 12/15 and another flight departs CO on 12/17 I would want to exclude both flights for the list.

If a flight departs CO on 12/17 and no other flight arrives CO on 12/5 or 12/16 I want to include the flight.

We want to know in advance when we need to position aircraft out of network to pick up passengers AND we want to know when aircraft are dropping off passengers out of network. We want to market these positioning flights but not if there are other passenger occupied flights already bringing the aircraft to the departure/arrival state.

I hope this all makes sense.

Below query describes actual fields.

SELECT

TRIPS.TripNumber
,ACCT.AccountName
,CONVERT(Datetime, LEGS.DepartureTime) as [DATE]
,LEGS.ArrivalTime
,ORIG.AirportICAOCode as [ORIG]
,DEST.AirportICAOCode as [DEST]
,ORIGSTATE.StateAbbreviation as [ORIGSTATE]
,DESTSTATE.StateAbbreviation as [DESTSTATE]
,PRI.LookupValue as [FARETYPE]

FROM tm_Legs as LEGS

INNER JOIN tm_Trips as TRIPS
ON TRIPS.TripID=LEGS.TripID
INNER JOIN tl_Airports as ORIG
ON ORIG.AirportID=LEGS.OriginAirportID
INNER JOIN tl_Airports as DEST
ON DEST.AirportID=LEGS.DestinationAirportID
INNER JOIN tm_Accounts as ACCT
ON ACCT.AccountID=TRIPS.AccountID
LEFT OUTER JOIN tl_States as ORIGSTATE
ON ORIGSTATE.StateID=ORIG.StateID
LEFT OUTER JOIN tl_States as DESTSTATE
ON DESTSTATE.StateID=DEST.StateID
INNER JOIN tl_Countries as ORIGCOUNTRY
ON ORIGCOUNTRY.COUNTRYID=ORIG.CountryID
INNER JOIN tl_Countries as DESTCOUNTRY
ON DESTCOUNTRY.COUNTRYID=DEST.CountryID
INNER JOIN tl_Lookups as PRI
ON PRI.LookupID = LEGS.PriorityTypeLookupID and PRI.LookupTypeID = 82

WHERE LEGS.StatusTypeID <>13
--Destination out of network
AND (ORIGSTATE.StateID IN (17,15,41,57,49,31) and DESTSTATE.StateID NOT IN (17,15,41,57,49,31))
--Origin out of network
OR (DESTSTATE.StateID in (17,15,41,57,49,31) and ORIGSTate.StateID NOT IN (17,15,41,57,49,31))
--Origin and destination out of network
OR (ORIGSTATE.StateID NOT IN (17,15,41,57,49,31) and DESTSTATE.StateID NOT IN (17,15,41,57,49,31))
--Origin or destination out of country
OR (ORIGCOUNTRY.CountryID IN (40,150) or DESTCOUNTRY.CountryID IN (40,150))
ORDER BY LEGS.DepartureTime ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 01:08:53

please explain us which column in your table will store the network related information and would help us in identifying the states within a network.
Easiest thing would be to post some sample data from your tables and give the output based on that. Refer to link in Bretts signature for tips on how to post relevant details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2011-12-23 : 14:29:51

ORIGSTATE --origin state
DESTSTATE --destination state
LEGS.DepartureTime --leg departure time
LEGS.ArrivalTime --leg arrival time

Basically I just need to compare these four values in filtering the results.

Hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 23:50:39

why not put the values in a table and then use join with that rather then using a series of IN conditions?
Also for your second part ( checking whether any other planes take off within 48 hours), you need to use a subquery using APPLY operator. if you can let us know the table structues with some sample data, then we can come up with a solution.
With current form, its difficult to make out what are fields in each tables and which fields have what type of data etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources