Query help for Route - 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

Query help for Route

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-18 : 08:31:54

Hi,



sample data...

create table #temp
(
FlightID int, 
SegmentID int, 
SegmentDepaturePlace varchar(10),
SegmentArrivaldatePlace varchar(10)
)
insert into #temp
select 1,1,'aaa','bbb' union all
select 1,2,'bbb','ccc' union all
select 1,3,'ccc','ddd' union all
select 2,1,'xxx','yyy' union all
select 3,1,'aaa','bbb' union all
select 3,2,'bbb','ccc' union all
select 3,3,'ccc','ddd' union all
select 3,4,'ddd','eee' union all
select 3,5,'eee','fff' union all
select 3,6,'fff','ggg' 
go

select * from #temp

Expected output.
FlightId Route
1 aaa-bbb-ccc-ddd
2 aaa-bbb
3 aaa-bbb-ccc-ddd-eee-fff-ggg

Here The first segments Arrival place is same as next segment Departure Place(If next segments exists).

A flight can have multiple segments .

Number of segments may differ from flight to flight.

Thanks,

--Ranjit

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 09:02:46

are you using SQL Server 2005 and later ?

KH
[spoiler]Time is always against us[/spoiler]

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-18 : 09:10:07

I am using Sql server 2005 and 2008 also.

I prepared Query for above scenario by assuming maximum segments as 10.
But I need a generalized query (It should work for more then 10 segments also)

Please help me

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-18 : 10:16:25

i prepared query


with cte as
(   
select       
  FlightID      
 ,convert(varchar(max),substring((SELECT ( '- ' +SegmentDepaturePlace)
    from  #temp c2      
    where c1.FlightID=c2.FlightID      
    order by FlightID      
     FOR XML PATH( '' )      
                  ), 3, 1000 )) as Departure 
from #temp c1      
--where FlightID=60 
group by FlightID
) 
, cte2 as
(
select   
   FlightID
  ,SegmentArrivalPlace
  ,row_number() over (partition by FlightID order by  segmentId) as N1  
from   
  #temp s
  ),cte3 as
  (  
select FlightID,max(N1) maxSegments 
from cte2
group by FlightID      
)
,cte4 as
(
select    
	c2.FlightID
   ,c2.SegmentArrivalPlace
from cte2 c2 
inner join cte3 c3 on c3.maxSegments=c2.N1  and c3.FlightID=c2.FlightID 
)
select 
	c.FlightID
	,c.Departure+'-'+ c4.SegmentArrivalPlace as Route
from cte c 
inner join cte4 c4 on c4.FlightID=c.FlightID

--Ranjit

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 20:15:38

[code]
; with cte as
(
select FlightID, SegmentID = 0,
Route = SegmentDepaturePlace
from #temp
where SegmentID = 1
union all
select FlightID, SegmentID,
Route = SegmentArrivalPlace
from #temp
)
select FlightID,
Route = stuff((
select '-' + Route
from cte x
where x.FlightID = c.FlightID
order by SegmentID
for xml path('')
), 1, 1, '')
from cte c
group by FlightID[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources