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

There's actually a much easier way (using Jason's set up data):

SELECT i, targetNode=item

FROM @yourTable

CROSS APPLY dbo.DelimitedSplit8K(someStringField, '_')

WHERE itemnumber = 3

Again, credit to Jeff Moden for the DelimitedSplit8K function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

He's the...

This may help.

DECLARE @T TABLE

(Col1 INT, Col2 INT, Col3 INT, Col4 INT

,Col5 INT, Col6 INT, Col7 INT, Col8 INT

...

There's nearly always a faster way:

DECLARE @a AS VARCHAR(1000)

SET @A = 'This is <<FirstName>> <<LastName>> FROM <<PLACE>>,...

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

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

elogen (9/26/2012)

---

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

elogen (9/26/2012)

---

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

CELKO (9/26/2012)

---

I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks like this: 1030 or 0735. I need...

Nice set up data Sean!

Using it, I'd like to offer a slightly less verbose solution:

SELECT *, WaitTime=DATEDIFF(minute

,CAST(check_in AS TIME)

,CAST(STUFF(appt_time, 3, 0, ':')...

Since the two CTEs are virtually identical, here's another option that may be a little cleaner (put the second one into a temp table):

SELECT RouteName=Routename +

...

aaron.reese (9/26/2012)

---

The challenge is trying to identify the journey sequence. For Example in route 1 you go from B to C and B to A. SQL is...

Note that one other thought occurred to me. You could have another route that's identical to other routes except that the origin/final destination are different, such as this:

ID ...

How about something like this?

DECLARE @StartDT DATETIME = '2011-04-01'

;WITH Dates AS (

SELECT TOP (1+DATEDIFF(day, @StartDT, DATEADD(day, -1, DATEADD(year, 1, @StartDT))))

...

Try this. Same set up data as Aaron but different temp table name.

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