The left join and the IN clause don't cancel because they aren't necessarily looking at the same records. The IN clause check that the FE code of the opportuinty matches...

declare

@int int, @lpad int

select

@int = 31

What is the underlying data in dbo.allNames.Surname and dbo.allNames.initials for these 4 records?

Well if Surname is not null, it' snot null. But a single null might not have the same effect under a different sort order, and you don't have a coalesce around...

[And this is the first reply copied from the original site:]

Hi A_Michigan_User,

 Erland has some techniques on his site to convert a comma-seperated

 string to a table with one value per row....

IN returns on the first match, too. In fact I'm pretty sure that the two statements are regraded as eqivaluent by the optimiser.

You still need to check the left joins...

You could put the code on the remote server (where the db list resides) and cahnge it to create a stored proc which could then be executed from yuor local server...

In the full version of your query, are you retrieving any fields from BL_D?

If not, are you using the join to BL_D to produce duplicate records in your result set? Since you...

A slight variant on the above that will extract a single block of numerals (<256) embedded in any string.

Incidentally, the clumsiness of this approach highlights the fact that strictly speaking...

Or:

 

select datename(m,datepart(m,monthdate)) [MONTH]

, count(*)  [Number of Requests Completed]

from MonthGroup

group by datename(m,datepart(m,monthdate))

I believe you should 'fully' normalise your OLTP system and if the queries get too difficult/expensive, develop a reporting platform (data marts etc.,etc.,etc.). It's the best way in the long...

Here's a more user-friendly version. You need to check whether you need all those left joins. (I've got rid of one which was overridden by an equi-join...

Here's one way:

declare

@input table(fl float)

--

insert

...

I thought the hash was granular enough that it wasn't worth either using a bigint or devising some fiendishly economical but unreadable hash algorithm.

BTW, I've updated the code correcting...

>I'm not sure what the point of the magnitude column is.

>I can't come up with a pair of numbers which would have the same hash, but different magnitudes....