-
Count
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|