It has been some time since I have converted outer joins from old style to new style format, however there are generally two points to bear in mind:

1. With the...

You need to SUM the CatchNumber. Something like the following should work:

SELECT A.ShipName, A.FishingDate, A.FishingLocation, D.Jack, D.Rose

FROM TABLE1 A

 JOIN (

  SELECT B.ShipName

   ,B.FishingDate

   ,SUM(CASE B.FishSpecies WHEN 'Jack' THEN CatchNumber END) AS Jack

   ,SUM(CASE B.FishSpecies...

BOL gives good documentation on locking. In brief it appears you were confused between the lock granularity,  ROWLOCK, PAGLOCK, TABLOCK etc and the lock type, shared, UPDLOCK, XLOCK etc.

In your...

For code like this to work you need to use UPDLOCK, or XLOCK, and have an index...

Do you mean * -1?

Something along the lines of the following should work:

SELECT Y.*

FROM YourTable Y

 JOIN (

  SELECT Y1.[ID], MAX(Y1.PAT_TO_DATE) AS PAT_TO_DATE

  FROM YourTable Y1

  GROUP BY Y1.[ID]) D

 ON Y.[ID] = D.[ID] AND Y.PAT_TO_DATE = D.PAT_TO_DATE

The question is not very clear.

If there are not too many rows (less that 10,000?) something like the following triangular join may

be what you want. With a lot of rows...

A trigger should work although it would probably be more efficient to code the logic when you INSERT or UPDATE. If you want to use a trigger, something like the...

This may work. It uses derived tables, only one column in the GROUP BY

 and an EXISTS subquery to avoid the overhead of a DISTINCT.

SELECT ISNULL(D.PCCount, 0) AS PCCount

 ,P.Postcode, P.Town,...

Assumming UkPostcodes.Postcode contains the postcode district and Customers_Trade.Postcode

contains the full postcode, how about:

SELECT ISNULL(D.PCCount, 0) AS PCCount

 ,P.* -- or whatever columns you want

FROM dbo.UkPostcodes P

 LEFT JOIN (

  SELECT P1.Postcode

   ,COUNT(*) AS PCCount

  FROM...

It is difficult to tell what you want and which columns are meant to match. In future please follow the instructions at http://www.aspfaq.com/etiquette.asp?id=5006

You may want to do something like...

 'Won"t Fix' should be 'Won''t Fix'

ie Embed two single quotes, not one double quote.

eg SELECT 'Won''t Fix' AS STRING

STRING   

---------

Won't Fix

(1 row(s) affected)

Unfortunately ROUND is a lot worse than FLOOR as six month ranges fail to show correctly.

dob       DateFrom      DateTo     RoundAge  CaseAge

--------  ------------  ---------  --------  -------

19460624  19461224      19470623    1         0

19460624  19471224      19480623   ...

Use the REPLACE function. Something like:

SELECT *

FROM TableA A

 JOIN TableB B ON A.PK = B.PK

WHERE REPLACE(REPLACE(A.ColWithCRLF, CHAR(13), ''), CHAR(10), '') <> B.ColWithoutCRLF

Floor nearly works but it produces a rounding error every 4 years as below:

dob      Birthday FloorAge    CaseAge     RoundingError

-------- -------- ----------- ----------- -------------

19460624 19460624 0           0           N

19460624 19470624 0           1          ...