Duplicate phone number report in SSRS

I am new to MS SQL Report Builder (3.0).
I need to list customer accounts with matching phone numbers.
I can do this in MS Access, but am not able to do it in Report Builder
Any help would be appreciated
my select statement is as follows
select *
from (
select 'PAC' as div, cmp_code,  cmp_name, cmp_tel
from [100].dbo.cicmpy
union
select 'CLA' as div,  cmp_code,  cmp_name, cmp_tel
from [200].dbo.cicmpy
union
select 'CTI' as div,  cmp_code,  cmp_name, cmp_tel
from [300].dbo.cicmpy
) xx

where
(len(cmp_tel)>9)

order by cmp_tel
I812Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
For starters, UNION eliminates duplicates, so use UNION ALL instead.

This will give you the duplicate phone numbers..
SELECT cmp_code,  cmp_name, cmp_tel, COUNT(cmp_tel) AS phone_count
FROM (
	select 'PAC' as div, cmp_code,  cmp_name, cmp_tel
	from [100].dbo.cicmpy
	UNION ALL
	select 'CLA' as div,  cmp_code,  cmp_name, cmp_tel
	from [200].dbo.cicmpy
	UNION ALL 
	select 'CTI' as div,  cmp_code,  cmp_name, cmp_tel
	from [300].dbo.cicmpy) a
GROUP BY cmp_code,  cmp_name, cmp_tel
HAVING COUNT(cmp_tel) > 1

Open in new window

I812Author Commented:
Thanks Jim!

I copied the SQL syntax
Now I get an error dialog box:

"Failed to Preview Report
The Value expression for the text box ‘div’ refers to the field ‘div’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case."

div references the 3 different dbos ... 100, 200 & 300
ValentinoVBI ConsultantCommented:
Jim didn't include the div field in his main select:

SELECT div, cmp_code,  cmp_name, cmp_tel, COUNT(cmp_tel) AS phone_count

You'll also need to add it to the group by:

GROUP BY div, cmp_code,  cmp_name, cmp_tel
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

I812Author Commented:
"COUNT(cmp_tel) AS phone_count"      produces errors
"GROUP BY"      produces errors
I812Author Commented:
I got Jim's syntax to work
(thanks Jim)
however, no records are showing

I am trying to show any records with matching phone numbers
is my grouping incorrect?

select *
from (
select 'PAC' as division, cmp_code,  cmp_name, cmp_tel
from [100].dbo.cicmpy
union all
select 'CLA' as division,  cmp_code,  cmp_name, cmp_tel
from [200].dbo.cicmpy
union all
select 'CTI' as division,  cmp_code,  cmp_name, cmp_tel
from [300].dbo.cicmpy
) xx

GROUP BY division, cmp_code, cmp_name, cmp_tel
HAVING COUNT(cmp_tel)>1
Tony303Commented:
Are there actually any duplicates in the raw data?

Look at this first...
 (perhaps and ORDER BY statement will be needed).


select *
from (
select 'PAC' as division, cmp_code,  cmp_name, cmp_tel
from [100].dbo.cicmpy
union all
select 'CLA' as division,  cmp_code,  cmp_name, cmp_tel
from [200].dbo.cicmpy
union all
select 'CTI' as division,  cmp_code,  cmp_name, cmp_tel
from [300].dbo.cicmpy
) xx

Open in new window

I812Author Commented:
That statement returns records
some have duplicate phone #s
SharathData EngineerCommented:
try this.
SELECT * 
  FROM (SELECT 'PAC' AS division,cmp_code,cmp_name,cmp_tel 
          FROM [100].dbo.cicmpy 
         WHERE ( LEN(cmp_tel) > 9 ) 
         GROUP BY cmp_code,cmp_name,cmp_tel 
        HAVING COUNT(cmp_tel) > 1 
        UNION ALL 
        SELECT 'CLA' AS division,cmp_code,cmp_name,cmp_tel 
          FROM [200].dbo.cicmpy 
         WHERE ( LEN(cmp_tel) > 9 ) 
         GROUP BY cmp_code,cmp_name,cmp_tel 
        HAVING COUNT(cmp_tel) > 1 
        UNION ALL 
        SELECT 'CTI' AS division,cmp_code,cmp_name,cmp_tel 
          FROM [300].dbo.cicmpy 
         WHERE ( LEN(cmp_tel) > 9 ) 
         GROUP BY cmp_code,cmp_name,cmp_tel 
        HAVING COUNT(cmp_tel) > 1) xx 
 ORDER BY cmp_tel 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.