October 31, 2007 at 5:43 am
need a T-SQL script that:
looks up a Account.Account field and sees whether the first 10 characters of any two accounts are the same
how do i do that?
October 31, 2007 at 5:50 am
Here it is...
SELECT T1.Account, T2.NoOfMatches
FROM Account T1
INNER JOIN
(
SELECT SUBSTRING( Account, 1, 10 ) AS Account,
COUNT( * ) NoOfMatches
FROM Account
GROUP BY SUBSTRING( Account, 1, 10 )
HAVING COUNT( * ) > 1
) T2 ON T2.Account = SUBSTRING( T1.Account, 1, 10 )
--Ramesh
October 31, 2007 at 6:25 am
Thank You,
One thing that happens is that the resulting table has:
(below is suppose to be a table)
Account----l-----NoOfMatches
Agcert-----l-----3
AgCert----l-----3
agcert-----l-----3
Is there now way of the result just being???
Account---l---NoOfMatches
Agcert----l----3
Dipesh---l-----4
etc.......
therefore grouping the reults?
October 31, 2007 at 7:04 am
Sounds like you just need an "ORDER BY"
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2007 at 10:09 am
If you just want the first 10 characters of the account then use the derived table in the queries.
SELECT SUBSTRING( Account, 1, 10 ) AS Account, COUNT( * ) NoOfMatches
FROM Account
GROUP BY SUBSTRING( Account, 1, 10 )
HAVING COUNT( * ) > 1
--Ramesh
November 1, 2007 at 6:17 am
Thank You for your help!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply