Hi Svetlana!

I think this is a nice introductory tutorial on using rCTEs.

However like a couple of folks that have already replied, I'd do it a little differently:

WITH Offices AS (

...

dwain.c (7/8/2013)

---

Queue is definitely still stuck, although I did just receive one notification on this...

reid.kell (7/8/2013)

---

Dwain.c, your code generates a single...

Queue is definitely still stuck, although I did just receive one notification on this thread...

Jeff Moden (7/8/2013)

---

L' Eomot Inversé (7/7/2013)

---

jasona.work (7/5/2013)

---

Koen Verbeeck (7/5/2013)

---

jasona.work (7/5/2013)

---

You *KNOW* it's going to be a slow day when you get in at your normal time, and get front-row parking...

I...

Excuse me for asking a dumb question here but the OP says the TransactionID is the primary key with presumably a clustered index on it (my assumption).

In that case, without...

Assuming your Excel sheet is accessible from the SQL Server, you can use Chrissy's INNER JOIN on an OPENROWSET to read directly from the Excel sheet.

Hoo-uh! And here I thought everyone had forgotten this thread.

I'm not surprised by the results. I've had to clock down the machine I did this timing test on...

I hesitate to say this but I don't think you've got enough information to solve this.

Any query that might appear to solve it would need to rely on the ordering...

Oh, I get it! It can be used like a productivity measure:

SLOC/lac-days

Sean Pearce (7/5/2013)

---

dwain.c (7/4/2013)

---

I don't...

Flattery will get you everywhere! 🙂

But, I actually saw the UNION ALL method the first time in the discussion...

What an odd requirement!

Mark - That's a pretty slick approach.

Using Eugene's suggestion of a Crosstab query, this is the best (or at least the least messy approach) I could come...

I don't suppose you'd know...

What, no takers? OK then I'll go for it.

Test harness (now 1M rows):

/**************************************************

(1) Sample Data

**************************************************/

IF OBJECT_ID('tempdb..table1') IS NOT NULL

DROP TABLE table1;

IF OBJECT_ID('tempdb..table2') IS NOT NULL

DROP TABLE table2;

IF OBJECT_ID('tempdb..tally') IS...