Question for AirLine Logic..

[devendrap]

Is there any website where we can find example for complicated SQL. Like for travel ticket for Airlines

Here is the Example.. From New York to Detroit to Chicago to Florida...

I have data in following pattern in the table:
TicketNno Place Date&Time
xx1 NewYork 2003-05-01 10.30AM
xx1 Detroit 2003-05-01 12.00Am
xx1 Chicago 2003-05-02 1.00AM
xx1 Florida 2003-05-03 4.00AM

I am looking for particular pattern, when Detroit to Chicago trip happen compare it in following format:

TicketNo Date (Detroit) Date(Chicago)Difference(hour)
xx1 2003-05-01 12.00AM 2003-05-02 1.00AM 3
xx2 xxxx yyyy zz


Appreciate your help.

Thanks,
Dave

 

[BillDHS]

You could start with a self join:

Here's a quick start, the date may need to be extacted from the timestamp to include in the WHERE clause and the RANK function.

Select Depart.Ticket#, Depart, DepartTime, Arrive, ArriveTime

From (select ticket#, place, time, RANK(time)
from tickethist
group by 1,2,3) DEPART(Ticket#, Depart ,DepartTime, DRank),
(select ticket#, place, time, RANK(time)
from tickethist
group by 1,2) ARRIVE(Ticket#, Arrive, ArriveTime, ARank)

Where Depart.Ticket# = Arrive.Ticket#
and ARank = DRank + 1

 

[devendrap]

Thanks that does help. The query works.

The Table has around 100 Million Rows b/c volume of data I am getting Spool space error for larger selection.

I am looking for Detroit to Chicago pattern only. I was trying to solve the query using subquery but no success yet.

 

[BillDHS]

In the query that works, add a WHERE clause to the depart derived table for Place = detroit and in the arrive derived table for Place = Chicago:

From (select ticket#, place, time, RANK(time)
from tickethist
WHERE Place = 'Detroit'
group by 1,2,3) DEPART(Ticket#, Depart ,DepartTime, DRank),
(select ticket#, place, time, RANK(time)
from tickethist
WHERE Place = 'Chicago'
group by 1,2) ARRIVE(Ticket#, Arrive, ArriveTime, ARank)

 

[dnoeth]

Nice puzzle ;-)

ct devendrap(TicketNo int,
LegNo int,
Place varchar (20),
TS Timestamp(0)
);

ins devendrap(1, 1,'New York', current_timestamp(0));
ins devendrap(1, 2,'Detroit', current_timestamp(0) + interval '01' hour);
ins devendrap(1, 3,'Chicago', current_timestamp(0) + interval '02' hour);
ins devendrap(1, 4,'Florida', current_timestamp(0) + interval '03' hour);

ins devendrap(2, 1,'New York', current_timestamp(0));
ins devendrap(2, 2,'Chicago', current_timestamp(0) + interval '01' hour);
ins devendrap(2, 3,'Detroit', current_timestamp(0) + interval '02' hour);
ins devendrap(2, 4,'Florida', current_timestamp(0) + interval '03' hour);

ins devendrap(3, 1,'New York', current_timestamp(0));
ins devendrap(3, 2,'Detroit', current_timestamp(0) + interval '01' hour);
ins devendrap(3, 3,'Florida', current_timestamp(0) + interval '02' hour);
ins devendrap(3, 4,'Chicago', current_timestamp(0) + interval '03' hour);

ins devendrap(4, 1,'New York', current_timestamp(0));
ins devendrap(4, 2,'Florida', current_timestamp(0) + interval '01' hour);
ins devendrap(4, 3,'Chicago', current_timestamp(0) + interval '02' hour);


Non stop flights only or Detroit-Chicago via New York also?

Do you have a leg number in your table?
Then it's quite easy:

select
d.ticketno,
d.ts as Depart,
a.ts as Arrival,
(Arrival - Depart) hour to minute as Diff
from
devendrap d, devendrap a
where d.ticketNo = a.ticketNo
and d.place = 'Detroit'
and a.place = 'Chicago'
and
--non stop flights only?
a.LegNo = d.LegNo + 1;

--or flights via ...?
Arrival > Depart;

Instead of a self join you could also use an aggregation:

select
ticketno,
min(case when Place = 'Detroit' then ts end) as Depart,
min(case when Place = 'Chicago' then ts end) as Arrival,
(Arrival - Depart) hour to minute as Diff
from devendrap
group by ticketno
having
--non stop flights only?
min(case when Place = 'Chicago' then legno end) -
min(case when Place = 'Detroit' then legno end) = 1;

--or flights via ...?
Arrival > Depart;


If you need non stop flights only and don't have a leg number, use an OLAP function to create it:
select
ticketno,
min(case when Place = 'Detroit' then ts end) as Depart,
min(case when Place = 'Chicago' then ts end) as Arrival,
(Arrival - Depart) hour to minute as Diff
from
(
select
ticketno,
place,
ts,
rank() over (partition by ticketno order by ts) as LegNo
from devendrap
) dt
group by ticketno
having
min(case when Place = 'Chicago' then legno end) -
min(case when Place = 'Detroit' then legno end) = 1;

Dieter