Forgot about no Decimal places:

DECLARE @D DECIMAL(18,9)

 ,@S VARCHAR(20)

 ,@R VARCHAR(20)

 ,@Pos SMALLINT

SET @D = 0.0

SET @s-2 = CAST(@D AS VARCHAR(20))

SET @r = REVERSE(SUBSTRING(@S, CHARINDEX('.', @s-2) +...

DECLARE @D DECIMAL(18,9)

 ,@S VARCHAR(20)

 ,@R VARCHAR(20)

SET @D = 0.8333

SET @s-2 = CAST(@D AS VARCHAR(20))

SET @r = REVERSE(SUBSTRING(@S, CHARINDEX('.', @s-2) + 1, 20))

SELECT LEN(SUBSTRING(@R, PATINDEX('%[1-9]%',

You are comparing two different datatypes with NULLIF( @memberid,-1). As INT has a higher precedence than VARCHAR, the VARCHAR will be implicitly converted to an INT before the comparison is...

>> Can you put SELECT statements inside that COALACSE statement???

You can, but the outer joins should produce the NULLs for you. Try:

SELECT E.EMPID

 ,COALESCE(L.PlantCode, H2.B_COMN_SITE_NO, 'N/A') AS HRLocation

 ,COALESCE(L.PlantCode, P2.B_COMN_SITE_NO, 'N/A') AS...

I am not sure what you are trying to do, but a brief look suggests that you may be able to get away with a select along the lines of:

SELECT...

FYI, the second version should be faster as it scans YourTable once instead of five times.

If possible, normalize the table.

If you have to live with it, something LIKE this should work.

SELECT D.u_id, D.job_id, D.Hours

FROM (

 SELECT u_id, job_id, m_hours as Hours, 1 as HourOrder FROM YourTable

 UNION...

An obvious typo on my part which is now corrected:

SELECT B.BookID AS Publication

 ,B.Title

 ,B.ISBN

 ,B.YearOfPublication

 ,P.PublisherName

 ,B.PlaceOfPublication

 ,B.Verified

 ,B.BookType

 ,P1.Surname + ' ' + P1.Initials

  + ISNULL(', ' + P2.Surname + ' ' + P2.Initials, '')

  +...

Assuming:

1. A book has at least one author

2. dbo.BookAuthor.[Sequence] goes from 1 for first author to 3 for third author

Try something like:

SELECT B.BookID AS Publication

 ,B.Title

 ,B.ISBN

 ,B.YearOfPublication

 ,P.PublisherName

 ,B.PlaceOfPublication

 ,B.Verified

 ,B.BookType

 ,P1.Surname + ' ' + P1.Initials

  +...

It could be a data type precedence problem.

eg If jrlid is a char it will automatically to promoted to a varchar and any indexes on jrlid will not be used....

Looking this up on th web the following function may be more accurate over short distances (read the contents of the link):

CREATE FUNCTION dbo.GreatCircleDistance2

(

 @Latitude1 float = NULL,

 @Longitude1 float = NULL,

 @Latitude2...

Just managed to have a quick look at this.

The problem seems to be in the function where rounding can cause the cosine to be greater than 1 when the co-ordinates...

Not sure about the insert but could try creating the temp table with default db collation.

create table #temp

(

 TreeID int not null

 ,Dist float not null

 ,AddressLine1 varchar(50) collate database_default not null

)

I do...

This looks like geocodes although float would be a better data type.

With geocodes Pythagoras’ therom  does not work very well away from the equator. (It fails completely at the poles!)

Spherical...

It could be any number of issues.

It may be a data type issue.

ie If AttributeID and/or DateID are not INTs (ie they could be tinyints or smallints) then they

will automatically...