March 31, 2011 at 12:36 am
Hi experts,
create table temp
(
FlightName varchar(20)
,SegmentID int
,SegmentNamevarchar(20)
,DeparturePlace varchar(20)
,DepartureDate datetime
,ArrivalPlace varchar(20)
,ArrivalDatedatetime
)
insert into temp
select 'KL123',1,'kl123a','aaaa','03/30/2011 02:15','cccc','03/30/2011 03:15'union all
select 'KL123',2,'kl123b','cccc','03/30/2011 03:30','dddd','03/30/2011 04:15'union all
select 'KL123',3,'kl123c','dddd','03/30/2011 04:30','bbbb','03/30/2011 05:15'union all
select 'KL126',1,'kl126a','dddd','03/30/2011 03:15','bbbb','03/30/2011 04:15'union all
select 'KL126',2,'kl126b','bbbb','03/30/2011 04:30','cccc','03/30/2011 05:00'union all
select 'KL128',1,'kl128','aaaa','03/30/2011 01:15','dddd','03/30/2011 03:45'
select * from temp
FlightNameSegmentIDSegmentNameDeparturePlaceDepartureDateArrivalPlaceArrivalDate
KL1231kl123aaaaa2011-03-30 02:15:00.000cccc2011-03-30 03:15:00.000
KL1232kl123bcccc2011-03-30 03:30:00.000dddd2011-03-30 04:15:00.000
KL1233kl123cdddd2011-03-30 04:30:00.000bbbb2011-03-30 05:15:00.000
KL1261kl126adddd2011-03-30 03:15:00.000bbbb2011-03-30 04:15:00.000
KL1262kl126bbbbb2011-03-30 04:30:00.000cccc2011-03-30 05:00:00.000
KL1281kl128aaaa2011-03-30 01:15:00.000dddd2011-03-30 03:45:00.000
Here
KL123 flight has 3 segments(aaaa-cccc ,cccc-dddd,dddd-bbbb)
KL126 flight has 2 segments(dddd-bbbb ,bbbb-cccc)
KL128 has only one segment(aaaa-dddd)
segment means Break journeys
Expected output:
FlightName[No.OfSegments]DeparturePlaceDepartureDateArrivalPlaceArrivalDate
KL1233aaaa2011-03-30 02:15:00.000bbbb2011-03-30 05:15:00.000
KL1262dddd2011-03-30 03:15:00.000cccc2011-03-30 05:00:00.000
KL1281aaaa2011-03-30 01:15:00.000dddd2011-03-30 03:45:00.000
Please help me in a query
--Ranjit
March 31, 2011 at 12:55 am
Solution 1:
select FlightName , NumberOFsegmets = COUNT(SegmentID)
, (select top 1 inner_temp.DeparturePlace from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID) DepartPlace
, (select top 1 inner_temp.DeparturePlace from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID DESC) ArrivalPlace
, (select top 1 inner_temp.DepartureDate from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID) DepartDate
, (select top 1 inner_temp.DepartureDate from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID DESC) ArrivalDate
from temp outer_temp
Group by outer_temp.FlightName
March 31, 2011 at 1:05 am
Hi,
Thanks for your response
My expected output
FlightName[No.OfSegments]DeparturePlaceDepartureDateArrivalPlaceArrivalDate
KL1233aaaa2011-03-30 02:15:00.000bbbb2011-03-30 05:15:00.000
KL1262dddd2011-03-30 03:15:00.000cccc2011-03-30 05:00:00.000
KL1281aaaa2011-03-30 01:15:00.000dddd2011-03-30 03:45:00.000
your query output:
FlightNameNumberOFsegmetsDepartPlaceArrivalPlaceDepartDateArrivalDate
KL1233aaaadddd2011-03-30 02:15:00.0002011-03-30 04:30:00.000
KL1262ddddbbbb2011-03-30 03:15:00.0002011-03-30 04:30:00.000
KL1281aaaaaaaa2011-03-30 01:15:00.0002011-03-30 01:15:00.000
Here
ArrivalPlace and ArrivalDate not matching with my expected output
March 31, 2011 at 1:32 am
Copy paste error bro 🙂
here is the corrected code:
-- solution 1
select FlightName , NumberOFsegmets = COUNT(SegmentID)
, (select top 1 inner_temp.DeparturePlace from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID) DepartPlace
, (select top 1 inner_temp.ArrivalPlace from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID DESC) ArrivalPlace
, (select top 1 inner_temp.DepartureDate from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID) DepartDate
, (select top 1 inner_temp.ArrivalDate from temp inner_temp
where inner_temp.FlightName = outer_temp.FlightName
order by inner_temp.SegmentID DESC) ArrivalDate
from temp outer_temp
Group by outer_temp.FlightName
March 31, 2011 at 2:28 am
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply