September 26, 2012 at 10:25 pm
Since you've indicated some flexibility in the route naming results, try this and see if it helps:
create table #Trips
(
ID int identity(1,1),
RouteID int,
Routename nvarchar(20),
Origin nvarchar(1),
Destination nvarchar(1)
)
insert into #Trips values (1,'ReturnTrip','A','B')
insert into #Trips values (1,'ReturnTrip','B','C')
insert into #Trips values (1,'ReturnTrip','C','B')
insert into #Trips values (1,'ReturnTrip','B','A')
insert into #Trips values (2,'ReturnTrip','A','B')
insert into #Trips values (2,'ReturnTrip','B','C')
insert into #Trips values (2,'ReturnTrip','C','B')
insert into #Trips values (2,'ReturnTrip','B','Q')
insert into #Trips values (3,'ReturnTrip','A','D')
insert into #Trips values (3,'ReturnTrip','D','C')
insert into #Trips values (3,'ReturnTrip','C','D')
insert into #Trips values (3,'ReturnTrip','D','A')
insert into #Trips values (4,'ReturnTrip','A','D')
insert into #Trips values (4,'ReturnTrip','D','E')
insert into #Trips values (4,'ReturnTrip','E','C')
insert into #Trips values (4,'ReturnTrip','C','E')
insert into #Trips values (4,'ReturnTrip','E','D')
insert into #Trips values (4,'ReturnTrip','D','A')
insert into #Trips values (5,'ReturnTrip','E','B')
insert into #Trips values (5,'ReturnTrip','B','C')
insert into #Trips values (5,'ReturnTrip','C','B')
insert into #Trips values (5,'ReturnTrip','B','E')
insert into #Trips values (6,'OneWayTrip','A','B')
insert into #Trips values (7,'OneWayTrip','A','B')
insert into #Trips values (8,'OneWayTrip','C','D')
insert into #Trips values (9,'OneWayTrip','C','E')
insert into #Trips values (10,'OneWayTrip','C','A')
insert into #Trips values (10,'OneWayTrip','A','E')
SELECT RouteName=Routename +
(SELECT TOP 1 Origin FROM #Trips b WHERE a.RouteID = b.RouteID ORDER BY ID) + 'To' +
(SELECT TOP 1 Destination FROM #Trips b WHERE a.RouteID = b.RouteID ORDER BY ID DESC) +
CASE (SELECT COUNT(*) FROM #Trips b WHERE a.RouteID = b.RouteID) WHEN 1 THEN '' ELSE 'Via' END +
SUBSTRING((
SELECT Origin + ''
FROM #Trips b
WHERE a.RouteID = b.RouteID
ORDER BY ID
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
,2, (
SELECT CASE COUNT(*) WHEN 1 THEN 2 ELSE COUNT(*) END
FROM #Trips b
WHERE a.RouteID = b.RouteID
)-1)
,RouteConcat=Routename + '-' + CAST(RouteID AS VARCHAR)
INTO #Temp
FROM #Trips a
GROUP BY RouteID, Routename
SELECT ID=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), RouteName
FROM (SELECT DISTINCT RouteName FROM #Temp) a
SELECT ID=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), RouteName, RouteConcat
FROM #Temp
DROP TABLE #Trips
DROP TABLE #Temp
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
September 26, 2012 at 10:45 pm
Dude you're awesome!
The modified queries now works for the scenarios I described.
I will apply the queries to some real data and see how that goes as well as try to understand how the funky for xml stuff works.
Again appreciate the time you took to help!
Cheers.
September 26, 2012 at 10:54 pm
The "funky for xml stuff" is actually the standard way to concatenate strings off of multiple records. Sorry I don't have a link to point you to handy. The part:
,TYPE).value('.', 'VARCHAR(MAX)')
Is used to make sure any special characters (to XML) get properly handled and probably isn't necessary for your case, it's just that my fingers have gotten used to typing it.
If you run into any further problems, don't hestitate to come back to this thread and ask. Can't always promise you I'm quite this responsive though. 😀
Happy this helped. Ended up being rather ugly.
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
September 27, 2012 at 5:40 am
@Dwain: you said you used ID to control the sequence. I explained in my first post that I thought that was dangerous because it is a field you have no control over. For example, if a route needs to be modified, the new route steps will have ID numbers which are out of sequence. This may not be an issue, but I would prefer the sequence to be controlled by a user maintained field.
@elogen.
you said in reply to my original post that you will always return through the same points as the outward journey. You then seem to contradict this in a later response.
If a route is either one-way or a return-trip, and you always return through the same points, you don't need the IB records as you can infer them from the OB records. If the return route is not always the same then you will need both IB and OB routes.
If you were to use a step seqence, you would not need to hold both the origin and destination. The result is less data storage and less risk of corrupted data. I am making the assumption that the problem presented is a simplified version of the real business issue. The business issue looks like it might be pick plans for a warehouse? If you can give us some insight on the actual business problem to be solved there may be a better (and totally different) approach.
September 27, 2012 at 5:54 am
aaron.reese (9/27/2012)
@Dwain: you said you used ID to control the sequence. I explained in my first post that I thought that was dangerous because it is a field you have no control over. For example, if a route needs to be modified, the new route steps will have ID numbers which are out of sequence. This may not be an issue, but I would prefer the sequence to be controlled by a user maintained field.
I used ID number only because it was handy. I agree that it would be better if there is a sequence number within each route to use instead.
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
September 27, 2012 at 6:26 am
Hi Aaron,
I meant to say that given a route, you would have to travel all points listed in the route. So, in the example A -> B, B -> C then returning via the same points, you cannot go back directly from C to A. However, there may exist another route that goes A -> B, B -> C and then directly C -> A, if this is the chosen route, then you may skip B on the return portion.
To give you some context, the business situation I'm trying to model are flights. You can have different type of flights eg: return, one way, open jaw etc..
The data I'm given is actually for a 1 off migration purpose so I'm just trying to aggregate the flight data into a form that will fit our system. So hopefully the sequence number isn't too much of an issue? Once the data is in our system, users are restricted to creating/modifying flights through a UI.
So I actually have flight departure data that I am trying to create a "flight" out of. The definition of a flight is a given combination of flight "legs". In the most straight forward example, this would be 4 legs, (A -> B, B ->C, C-> B, B->A). Now it would be incorrect to have say, 2 flights (per airline) that have exactly the same legs, this is why I have to go through each grouping of flight departures to see if they are in fact the same flight. (I already have the grouping of flight departures in a table, so I know how to bundle up the flight departures).
I hope what I am doing is correct 🙂
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply