Jeff,

You win hands down for elegance and simplicity and even though I knew how unlikely it was, I was still hoping to beat you out on performance. So I...

You can try something like this and but it will also match ABC in 0ABC (if this is what you want):

DECLARE @clients TABLE (clientno VARCHAR(20))

INSERT INTO @clients

SELECT '55566' As clientno

UNION...

Grecci,

I'm glad my post helped to jump start the discussion.

I suspected it might have issues with more XML but I was lazy to build it myself. Mark's is spot...

You can use Mr. Celko's view but if you must have a scalar function, you can do it with the following (I'll let you figure out how to add the...

Jeff,

The following thought occurred to me last night but I didn't get a chance to test it until this morning.

Isn't the modulo function designed to always return a positive integer?

Hence,...

GPO said:

The...

Not sure if this example is any more efficient than the one provided by bitbucket but it is an alternate:

DECLARE @times TABLE ([hh:mm]VARCHAR(12))

INSERT INTO @times

SELECT '115:32'

UNION...

It would probably help if your XML was properly formatted (corrected below).

Does this give you what you're looking for?

DECLARE @data XML

SET @data = CAST(

'<Automobile>

<Car name="Corvette">

<Properties color="Red"...

Outstanding article Jeff! Just what the doctor ordered for something I'm working on at this instant.

I can't wait for the purists to berate you for using "pseudo" random numbers...

I believe this thread is on the same subject:

http://www.sqlservercentral.com/Forums/Topic1260577-338-1.aspx#bm1260601

Try it as a correlated subquery and lose the GROUP BY, thusly:

DECLARE @HOF TABLE (

FamilyID INT, HoFName CHAR(4), Age INT, Gender CHAR(1), DoorNo CHAR(3), TotalDependents INT)

DECLARE @Dep TABLE (

FamilyID...

Jeff,

This is interesting. I've read about but never used the variant type but I can (now that you mention it) appreciate how it could be useful in an audit...

8-bit Ninja: Good on you for taking Lynn's advice and working the problem.

Just my opinion here but I think this looks a bit more elegant updating through a CTE. ...

To Tom-John and Janus Lin, sorry for the late reply on this but I believe I can address your "Top-N Fudge Rounding" approach with the sample code below. In...

Mr. edobrzel ,

When i run with your MNO I get this result set which adds to 100:

key1key2WeightAllocAmt

MNO116.0015.00

MNO216.0017.00

MNO316.0017.00

MNO416.0017.00

MNO516.0017.00

MNO616.0017.00