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          ...

Kenneth,

Nice use of modulus but your age calculation is wrong.

eg. A child will come out as 1 year old when he/she is one day old.

Looking back in this thread, my...

sp_execute and sp_executesql are not the same thing.

sp_execute is used with prepared statements by client APIs. (ODBC, ADO.NET etc)

sp_executesql is used by tsql for dynamic queries.

You will need to use a transaction and have an UNIQUE CONSTRAINT/INDEX on Number. Something like:

SET XACT_ABORT ON -- or add your own error handling

DECLARE @NextNumber int

BEGIN TRANSACTION

-- This will...