-
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
-
Hold on .... I'm rewriting my post .....
-
Try this:
--SELECT ALL IDS INTO TEST TABLE
SELECT CLIENTNO,ID INTO #TEST FROM ADATA
--SELECT THE IDS THAT HAVE A DIFFERENT CLIENT NUMBER
Select IDNO, IDNO10, ClientNo, theCount
From #test c
Inner Join (SELECT Left(a.IDNO, 10) As IDNO10, Count(*) As theCount)
FROM
#TEST a
,#TEST b
WHERE Left(a.IDNO, 10) = Left(b.IDNO, 10)
AND a.CLIENTNO <> b.CLIENTNO
GROUP BY Left(a.IDNO, 10)
HAVING COUNT(*) > 1) As d on d.IDNO10 = Left(c.IDNO, 10)
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
|
|