Look for articles/books by Itzik Ben-Gan. He is great with T-SQL (I think's that his entire job :-)).

I also liked Ken Henderson's stuff, particularly "The Guru's Guide to Transact-SQL"....

For max variance only though.

I believe it is still possible to find the NextMonth before getting additional specs.

I'm trying to avoid passing thru the original input table, or parts of it, multiple times.

Hopefully the last version is understandable enough while still holding the full passes of the...

Ok, thought about it some more: I think below is a 100% reliable way of finding the next month. This code also can handle duplicate months in the original...

Yep. NEWID() will generate a random value [ok, for the hyper-technical, no intel server can generate a truly random number, but it's good enough for this :-)]. So...

If there can be gaps in the months, and it appears from your test data there can be, I think you will need to resort to a scalar function to...

>> It will work with any months as long as the last month isn't more than 5 months after the first month. That would be an assumption though, unreliable at...

Hmm, it would be helpful if someone could re-point me to the data that my code fails on. I don't see the bug, so sample data would help me...

How about this? We let each person view the code and decide which they like better? Fair enough?

Mine:

SELECT

PersonId,

CASE...

When I get a chance, I will write code to calc the variance of *any* set of numbers.

At any rate, what solution did *you* provide? NONE. All you...

From the original q:

The idea is to find the earliest month value from the list of values per person only when the max variance is <= 2 taking into consideration...

>> but when you post junior level code (non-SARG-able queries anyone?) <<

Yes, I took the short-cut, because as *I* pointed out the code will result in full scan anyway.

When Lynn...

Depends. I was answering the request to select only rows with a gap of 2 or less (i.e....

SELECT product, MAX(description) AS description, max(price) AS price

FOM table

GROUP BY product

--ORDER BY product

If you couldn't glean enough info to work on this from the requestor's initial posts, it's not (only) the clients that are clueless!