As John suggested, note you'll need to use DATETIME rather than SMALLDATETIME

WITH CTE AS (

SELECT callref

, repid

, updatetime = CAST(updatetime AS DATETIME)

, rn = ROW_NUMBER() OVER (PARTITION BY callref ORDER BY CAST(updatetime AS DATETIME) )

FROM #updatedb)

SELECT a.repid,

num=COUNT(*)

FROM CTE a

WHERE EXISTS (SELECT * FROM CTE b WHERE b.callref = a.callref AND b.rn = a.rn + 1 AND b.repid <> a.repid)

GROUP BY a.repid

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537