I WANT TO SELECT IDs THAT HAVE THE FIRST 10 CHARACTERS THAT ARE EQUAL BUT WITH A DIFFERENT CLIENTNO.
hOW DO i ACHIEVE THAT THIS IS MY QUERY

--SELECT ALL IDS INTO TEST TABLE
SELECT CLIENTNO,ID INTO #TEST FROM ADATA

--SELECT THE IDS THAT HAVE A DIFFERENT CLIENT NUMBER
SELECT
a.CLIENTNO
,a.IDNO
,COUNT(*) AS [COUNT]
FROM
#TEST a
,#TEST b
WHERE SUBSTRING(a.IDNO,1,10) AS IDNO = SUBSTRING(b.IDNO,1,10) AS IDNO
AND a.CLIENTNO <> b.CLIENTNO
GROUP BY a.CLIENTNO,a.IDNO
HAVING COUNT(*) > 1