April 7, 2014 at 12:30 pm
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
SELECT *
FROM #XY123
DROP TABLE #XY123
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!
April 7, 2014 at 12:39 pm
How would you know that it's MIA-ATL-MIA instead of ATL-MIA-ATL? Remember that there's no default order on sql server.
April 7, 2014 at 1:07 pm
April 7, 2014 at 1:08 pm
Hi, Thanks for the reply.
Sorry, forgot to mention, there is a seq number.
CREATE TABLE #xy123
(
tktamt MONEY,
departcty VARCHAR(5),
arrivalcty VARCHAR(5),
tktnum INT,
seqnum INT
)
INSERT INTO #xy123
SELECT '100.00',
'DFW',
'LGA',
12345,
1
UNION ALL
SELECT '120.00',
'MIA',
'ATL',
23456,
1
UNION ALL
SELECT '120.00',
'ATL',
'MIA',
23456,
2
UNION ALL
SELECT '250.00',
'JFK',
'PHX',
34567,
1
UNION ALL
SELECT '250.00',
'PHX',
'SFO',
34567,
2
UNION ALL
SELECT '250.00',
'SFO',
'PHX',
34567,
3
UNION ALL
SELECT '250.00',
'PHX',
'JFK',
34567,
4
SELECT *
FROM #xy123
DROP TABLE #xy123
April 7, 2014 at 2:18 pm
This might become confusing, but I believe that it could help you solve your problem.
Try to understand it and post any questions that you have.
WITH Limits AS(
SELECT tktnum,
MAX( seqnum) Maxseq,
CAST( CEILING( CAST( MAX( seqnum) AS decimal(10,2)) / 2) AS int) AS arrivalseq
FROM #xy123
GROUP BY tktnum
),
rCTE AS(
SELECT x.tktamt,
x.departctyorigin,
x.departcty,
x.arrivalcty,
x.tktnum,
x.seqnum,
l.Maxseq,
CASE WHEN x.seqnum = l.arrivalseq THEN x.arrivalcty END destination
FROM #XY123 x
JOIN Limits l ON x.tktnum = l.tktnum
WHERE seqnum = 1
UNION ALL
SELECT r.tktamt + x.tktamt,
r.origin,
x.departcty,
x.arrivalcty,
x.tktnum,
x.seqnum,
l.Maxseq,
ISNULL( CASE WHEN x.seqnum = l.arrivalseq THEN x.arrivalcty END , r.destination)
FROM #XY123 x
JOIN Limits l ON x.tktnum = l.tktnum
JOIN rCTE r ON x.departcty = r.arrivalcty
AND x.tktnum = r.tktnum
AND x.seqnum = r.seqnum + 1
)
SELECT tktamt,
origin,
destination,
tktnum
FROM rCTE
WHERE seqnum = Maxseq
ORDER BY tktnum
April 7, 2014 at 4:00 pm
Hi
Here's my attempt at it. It should also handle situations where a direct flight is taken back to the origin. Sorry it's a bit ugly looking:-)
WITH presort AS (
SELECT tktnum, tktamt, departcty, arrivalcty, seqnum
,ROW_NUMBER() OVER (PARTITION BY tktnum, arrivalcty ORDER BY seqnum) acvisitnum
,COUNT(*) OVER (PARTITION BY tktnum) legCount
,SUM(tktamt) OVER (PARTITION BY tktnum) sumTktAmt
FROM #xy123
),
sorting AS (
SELECT tktnum, departcty, arrivalcty, seqnum, sumTktAmt
,legCount
,MAX(CASE WHEN acvisitnum = 1 AND seqnum <> legcount THEN seqNum END) OVER (PARTITION BY tktnum) midpoint
FROM presort
WHERE acvisitnum = 1 or seqNum = 1 and seqnum <> legcount
)
SELECT MAX(sumTktAmt) tktamt,
MAX(CASE WHEN seqNum = 1 THEN departcty END) origin,
MAX(CASE WHEN seqNum = midpoint OR legCount = 1 THEN arrivalcty END) destination,
tktnum
FROM Sorting
GROUP BY tktnum;
April 8, 2014 at 12:45 am
Hi Micky, awesome query, Can you explain how it works. I am a newbie to this.
April 8, 2014 at 1:29 am
There is likely to be a better solution, but the concept was to count the the number of visits to each airport. The first visit would be on the out leg and the next on the in leg. Then it was a matter of finding the max seqnum for the out legs. Once that was done you group up the row with the first row in the sequence.
I think this would be easier to do in sql server 2012, but haven't really looked into it.
April 8, 2014 at 2:06 pm
Thanks you Luis C and Micky! Both the solutions worked like charm.
April 8, 2014 at 10:47 pm
mickyT (4/7/2014)
HiDwain Camps did a nice article about arrival and departures here. Departures from Origins and Arrivals at Destinations[/url]
Thanks for the plug Micky!
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 8, 2014 at 11:43 pm
;with cte
as
(
SELECT departcty ,arrivalcty ,departcty as Firstdepartcty, CAST(arrivalcty AS VARCHAR(6)) as Firstarrivalcty ,seqnum,tktamt,tktnum
FROM #xy123 where seqnum = 1
UNION ALL
SELECT s.departcty ,s.arrivalcty , Firstdepartcty as Firstdepartcty,
CASE WHEN s.arrivalcty = C.departcty AND LEFT(Firstarrivalcty,1)!='_' THEN CAST('_'+s.departcty AS VARCHAR(6)) ELSE Firstarrivalcty END as Firstarrivalcty
,s.seqnum,tktamt= c.tktamt+s.tktamt,c.tktnum
FROM #xy123 s inner join cte c on s.departcty = c.arrivalcty and s.seqnum = c.seqnum +1 and s.tktnum = c.tktnum
)
select E.Firstdepartcty,SUBSTRING(Firstarrivalcty,IIF(LEFT(Firstarrivalcty,1)='_',2,1 ),LEN(Firstarrivalcty)),E.tktnum,tktamt
from cte E
INNER JOIN (select tktnum,Firstdepartcty,MAx(SeqNum) SeqNum from cte group by tktnum,Firstdepartcty )C
On E.Firstdepartcty = C.Firstdepartcty
AND E.seqnum = C.SeqNum
AND E.tktnum = C.tktnum
Regards,
Mitesh OSwal
+918698619998
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply