Sorry this is a bit of a "It Depends" answer. If you are only going to have up to eight courses then the following will do what you want.

WITH...

You could try the following

SELECT *, ISNULL(DATEDIFF(mi, LAG(CreateDate,1) OVER (ORDER BY ID), CreateDate), 0) AS Duration

FROM MyTable

MSDN LAG reference

Edit:

To refine it a bit more by using default and removing isnull

SELECT...

That's excellent

Thanks for the giggle 😀

I've removed the rcte version as that appeared to be behaving badly. The looped version appears to work OK.

To avoid issues where distances in the procedure get calculated badly...

**Insert bad word here**, I've either translated the python wrong or it doesn't work.

I've just tried it with some of my real data rather than the mocked up points I...

Thinking about this a bit more. If you replace the distance calculation in the current version with the haversine formula, you should get a reasonably accurate median.

So replace

denomsum =...

The floating...

Basically what you want to do is a Cross Tab query or a Pivot.

Here's a couple of links

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

So a crosstab example for your query would be

SELECT a.StudentID,

MAX(CASE WHEN Reader#...

While I'm on a bit of a roll. Had a bit of success with a rCTE, but it does require a iTVF to be created.

Removed: Incorrect results

Looking at it further, it may not be so bad in t-sql.

Here's my crack at it. It currently uses a while loop because I'm having a few issues turning...

I've had a quick look at this and I suspect while it could be done in T-SQL (painfully and slowly :-D), a CLR function built around your C++ would probably...

Good question thanks.

andyscott (9/16/2014)

---

A breakthrough! I've managed to find a way to sort the individual line segments into order, reversing those that were "backward" with respect to the others. In the process...

Eirikur Eiriksson (9/15/2014)

---

Quick thought, don't ever use geometry for geographical data, use geometry with the appropriate SRID, normally 4326.

mickyT's solution is straight forward, just make certain that the direction...