CASE

WHEN t.SoldYR1 <> 0 THEN CAST(ROUND(100.0E * (t.SoldYR1 - t.SoldYR2) / t.SoldYR1, 0) AS VARCHAR(11)) + '%'

ELSE '0%'

END

Pure luck I guess.

More explanation about problem is found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113928

Try this

SELECTvd.CategoryID,

vd.CategoryName,

vm.MfrID

FROM(

SELECTCompanyID,

DivisionID,

DepartmentID,

VendorCategoryID

FROMGICSPF.dbo.GICSPFVendorCategoryHeader

WHEREVendorID = 14

) AS vh

INNER JOIN(

SELECTCompanyID,

DivisionID,

DepartmentID,

VendorCategoryID,

CategoryID,

CategoryName

FROMGICSPF.dbo.GICSPFVendorCategoryDetail

) AS vd ON vd.CompanyID = vh.CompanyID

AND vd.DivisionID = vh.DivisionID

AND...

Just beware that CHECKSUM can return a negative value.

Use ABS(CHECKSUM(NEWID())) to get positive random values.

For records {1, 2} OP wants the second record with ID 2 because there is a duplicate record.

For records {7, 8, 10, 11} OP wants the first record with ID...

Also see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112721

http://www.sqlservercentral.com/Forums/Topic562564-338-1.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3829716&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3928920&SiteID=17

Test ackonwledged.

Updated algorithm to get not only valid postcodes, but also current postcodes in use today.

See http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Validate-UK-postcode.aspx

Soft partioning

http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx

Hard partitioning

http://weblogs.sqlteam.com/peterl/archive/2008/06/12/Horizontal-partitioning-Enterprise-style.aspx

What is the expected output based on the provided sample data?

Easy fixed when you understand the code.

DELETEa

FROM@Sample AS a

INNER JOIN(

SELECTMIN(recID) AS recID,

col1,

col2,

col3,

userID

FROM@Sample

GROUP BYcol1,

col2,

col3,

userID

) AS b ON b.userID = a.userID

AND b.recID <> a.recID

WHERE(a.col1 = b.col1 OR a.col1 = '')

AND (a.col2 =...

It turns out you can write the algorithm even cleaner.

I got a comment on my blog to use an algorithm like this instead.

DELETEa

FROM@Sample AS a

INNER JOIN@Sample AS b ON b.userID...

Steve Jones - Editor (10/15/2008)

---

Multiple passes would be looking for a pattern like Row 2, where Col1 and 2 match, but Col3 is a null/blank. Then a second pattern would...

What about this sample data?

SELECT'', '', 'Y', 3 UNION ALL

SELECT'', 'X', 'Y', 3

Should first line be deleted since it is a subset of second line?