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...

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...

Ok,

To test the scenario that route 1 and route 2 start at the same origin but finish at different desitionations:

insert into #Trips values (1,'ReturnTrip','A','B')

insert into #Trips values (1,'ReturnTrip','B','C')

insert into #Trips...

Sorry posted twice, thought I'd lost the 1st reply...

There are possibilities I think that it may fail if a return route doesn't exactly retrace the same steps, especially if it is through different way points. That may end...

There are possibilities I think that it may fail if a return route doesn't exactly retrace the same steps, especially if it is through different way points. That may end...

Wow thanks for the query dwain,

it seems to work beautifully!

I'm just going to play around with some other scenarios that I've found with the data I'm given.

It seems that...

Thanks aaron,

Yes I fogot to include a vital column Direction which would indicate whether the journey was an Inbound or an Outbound.

So the code should look like this:

create table test.elogen

(

ID...

Thanks Paul, adding HASH to the left outer joins did indeed result in the double bitmap plan and also forces the order of the joins.

Cheers.

Well what do you know, the inline string split has resulted in a parallel execution plan!

Here is my query:

SELECT

CASE WHEN Link.ProcessType = 'D' THEN (SELECT ExecutionTime FROM MARSX.dbo.[DelimitedSplit8K](AI.LoadExceptionsFileName, '|')) END...

Thanks for the suggestion, but I think that my problem doesn't actually have anything to do with how I've defined my function.

For example, I tried redefinig the ufn as:

ALTER FUNCTION...

Thanks again Paul for the detailed explanation.

You're exactly right, my problem really had nothing to do with isolation levels and was more to do with the inefficiency of how I...

Thanks Paul for your answer and the link.

I guess what I need for my purposes is to set the isolation level to SNAPSHOT since my function will actually be a...

Hi Paul,

I've just tried your code, and yes it's returning..I missed the BREAK part....

thanks

Thanks for the suggestions guys,

but I'm wondering with the WHILE 1 = 1 method, wouldn't this mean that once the stored procedure is called, it would never return? So would...