select depart and arrival cities 1

[northw]

Hi all,
I have a question regarding the selection of arrival and departure city.

 CREATE TABLE #XY123
  (
     tktamt     MONEY,
     departcty  VARCHAR(5),
     arrivalcty VARCHAR(5),
     tktnum     INT
  )

INSERT INTO #XY123
SELECT '100.00',
       'DFW',
       'LGA',
       12345
UNION ALL
SELECT '120.00',
       'MIA',
       'ATL',
       23456
UNION ALL
SELECT '120.00',
       'ATL',
       'MIA',
       23456
UNION ALL
SELECT '250.00',
       'JFK',
       'PHX',
       34567
UNION ALL
SELECT '250.00',
       'PHX',
       'SFO',
       34567
UNION ALL
SELECT '250.00',
       'SFO',
       'PHX',
       34567
UNION ALL
SELECT '250.00',
       'PHX',
       'JFK',
       34567

--DROP TABLE #test1

SELECT *
FROM   #test1

There are One way, Round trips and journey with connecting flights in the above data, what I am looking for a report is tktamt, deprt city, arrival city and tktnum in a single line for each trip.
SO if it is one way trip or round trip there is no issue, if it is a trip with connecting flights tktnum has more than four records. In the above case for tktnum 34567 there are four records, for which I have to display only one record, where the depart city is 'JFK and Arrival city is 'SFO' as well as amount and tktnum.

Please suggest any solutions.

Thanks in advance!

 

[northw]

Code has some errors, here is the edited:

CREATE TABLE #XY123
  (
     tktamt     MONEY,
     departcty  VARCHAR(5),
     arrivalcty VARCHAR(5),
     tktnum     INT
  )

INSERT INTO #XY123
SELECT '100.00',
       'DFW',
       'LGA',
       12345
UNION ALL
SELECT '120.00',
       'MIA',
       'ATL',
       23456
UNION ALL
SELECT '120.00',
       'ATL',
       'MIA',
       23456
UNION ALL
SELECT '250.00',
       'JFK',
       'PHX',
       34567
UNION ALL
SELECT '250.00',
       'PHX',
       'SFO',
       34567
UNION ALL
SELECT '250.00',
       'SFO',
       'PHX',
       34567
UNION ALL
SELECT '250.00',
       'PHX',
       'JFK',
       34567

--DROP TABLE #XY123

SELECT *
FROM   #XY123

 

[gmmastros]

Is this for a class?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[northw]

Hi George,
Thanks for the reply, I am an analyst, with basic sql knowledge. This is not something for class.

 

[RyanEK]

Hi,

Will this work for you?

;with a as
(
	SELECT row_number() over (partition by tktnum order by tktnum) as idx, *
	FROM #XY123
),
b as
(
	select *, case when exists (select idx from a as a2 where a.tktnum = a2.tktnum and a.arrivalcty = a2.departcty and a2.idx < a.idx) then 1 else 0 end as visited
	from a
)

select tktamt, departcty, (select top 1 arrivalcty from b b2 where b2.tktnum = b.tktnum and visited = 0 order by idx desc) as arrivalcty, tktnum
from b 
where idx = 1

Results:

tktamt	departcty	arrivalcty	tktnum
100.00	DFW	LGA	12345
120.00	MIA	ATL	23456
250.00	JFK	SFO	34567

 

[northw]

Thank you Ryan! worked like a charm!