Wow, that works quite well. Given that I discovered some flaws in my post where I thought I had it with the quirky update, I'll try using this against my...

Hokay, so I think I have an algorithm to do this with a quirky update. There may be some bugs in it because there are so many combinations of how...

Sure. So turning the data set on its side, you could think of the sequence of values like this:

5,3,2,3,1,4,6....

The update you described works if you just want to know the...

Thanks Luis. The only problem there is it doesn't work for a rolling window.

If the highest value is at the tail end of the window and you iterate one row,...

It's not clear to me what the transformation is that you're trying to achieve here. Also, iterating over names doesn't make a whole lot of sense. Are you trying to...

Of course max() is aggregation; I don't think that was ever in question. However for the purposes of what's trying to be accomplished here, using max() in the context of...

Here it is without dynamic sql (use the same data population header)

;with cte as

(

select

TeamID,

...

Alright, I think (I hope) i have a query that will work for you, and it shouldn't matter if there are 2 coaches or 200. This query is kind of...

I figured it was probably for some sort of report; non-database people are the only ones who like de-normalized reports 🙂

FWIW, whether you use a cross tab pivot such as...

If MemberTypeID 2 is all you care about, you can simply omit the lines of code from Eirikurs example that reference MemberTypeIDs you don't care about.

Is this for a report...

Copy paste works nicely.

Alternatively, if you want it to be written directly to an excel document, the easiest way (after copy/paste) would be to output the results to file. You...

You shouldn't be looking to serialize the results (unless you're doing something like a dynamic pivot, or just formatting things for results. You can do a join in your update...

Exchange holidays are a whole other set of fun, which, for simplicities sake, I've excluded. I'm guessing if I can define a way to skip over holidays, it could similarly...

Yes, you need all the lines of the case statement. There's no way for it to determine the cases if you omit them.

Since none of the tables in any of those queries are available to us (the forum users), it's impossible to give much useful insight on what needs to be done...