Hi

Thought I would have a go at this. I've used a CTE to order and number the results by course year.

I've also created the statistics in separate queries to...

Hi

If you change the format of the replacement strings to be a single string of pairs then you could create a stored procedure to do a dynamic sql like the...

Hi

If you are working with Lat/Lon coordinates, you probably should use the Geography data type rather than the Geometry. Unfortunately these data types do use float to store...

Always love a puzzle 🙂 so here's my solution. I'm not sure if my grouping method will work further down the line, however it seems to work for 10...

dwain.c (12/12/2012)

---

2008 comes with the...

Hi Dwain

After reading your article (nice work), I couldn't help myself and added a Geography column to the airports table.

This allows the distance to be calculated using the Geography method...

Assuming that your fields are always as described you could use. However this probably won't perform as well as the 3 and 4 function methods.

select parsename(replace(last_name,'_','.'),1)

With the substring method...

bj_shenglong

I have one question for this test.

It seems the test data starts at 1999-12-01, but queries use '2012-01-01' to start. I am not sure what the end date is.

I was...

So comparing the islands and recursive queries returning similar rows

;WITH cte AS (

SELECT name,

DATEADD(mm, - ROW_NUMBER() OVER (ORDER BY name, saledate), saledate) dategroup,

saledate

FROM #sales

WHERE quantity > 2 ...

Using Jeff Moden's article http://www.sqlservercentral.com/articles/T-SQL/71550/ about Group Islands of Contiguous Dates as inspiration, you could do the following

;WITH cte AS (

SELECT name,

DATEADD(mm, - ROW_NUMBER() OVER (ORDER BY name, saledate),...

Hi

I've tried a slightly different approach. This will allow set of values with a chance value (weight) against to be used.

;WITH chances(RES, CHANCE) AS (

-- Range of values...

Hi Pedro

This should do what you want. I changed the DupKeys cte to use a group by and grabbed the minimum object_id as parent, then joined the DupKeys to...

dwain.c (11/29/2012)

---

To all who participated in this thread:

You may wish to review this new article:

http://www.sqlservercentral.com/articles/String+Manipulation/94365/

It provides a good utility function for solving this case and many other similar ones. ...

Hi

The following recursive option might work. It walks up and down from the queried invoice and joins the results.

DECLARE @invoiceID int = 3

;with rListUp as (

select a.invoiceid invoiceid, a.invoiceid...

I was working on something very similar to Jason's

;with

unpivoted as (

select [date], dense_rank() over (order by [date] desc) seq, value

from #test a

unpivot (value for col in (c1, c2,...