Jeff -

I read this article last week and it was definitely an eye-opener. Great stuff!

It is always exceedingly important to know your tools and most especially their limitations.

Sorry I...

Here's an article that shows how you can calculate a depreciation schedule for an asset using an rCTE: http://www.sqlservercentral.com/articles/T-SQL/90955/.

That example (among many others) is about mid-way through.

It can easily be...

Jeff Moden (8/8/2012)

dwain.c (8/8/2012)

Jeff Moden (8/8/2012)

If you're using...

I do agree that less is usually better ... except of course when it's not. 😛

An alternate to Lynn's solution may be something like this:

SELECT a.FlightNbr, a.DeptDateTime, a.Origin, a.Destination, a.FirstName, a.CustomerID

,a.SeatRow, HistoryAction

FROM (

SELECT FlightNbr, DeptDateTime, Origin, Destination,FirstName,...

Jeff Moden (8/8/2012)

If you're using the "counting" rCTE...

Hmmm... Strange requirement indeed. If I'm understanding this correctly, a quirky update might do it.

Set up your table like this:

create table #abc

(insertid int, cityid int,...

Shoot man! You should see the bloody (and I mean that literally) politics in Thailand!

Thanks for the feedback Prasad. Hope you find the CAV UNPIVOT useful someday.

In a continuing quest to understand what I'm seeing on performance I ran another test and thought...

All things considered, both of these approaches are incredibily fast.

I can't recall ever seeing a 1M row test harness return both CPU and elapsed ms in single digits before.

Nice work...

Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.

The performance test result is interesting.

Try...

Thanks Lynn! Apparently if I could read, I would've come up with this version instead:

;WITH Unpivoted AS (

SELECT SchoolCode, RaceCode, Code, Value

...

Here's a link to an article that covers passing in delimited strings to substitute for an array:

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

Another great one by Jeff Moden!

You can try something like this, although I don't get the same counts as you do:

;WITH Unpivoted AS (

SELECT RaceCode, Code, Value

FROM...

Nice work Lowell! I know I've learned a few tricks from some of your 10K posts!

I just hit 1,000 today. Gotta ways to go before they're redeemable.