asked on

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

Jim Horn

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

I812

ASKER

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

ValentinoV

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

I812

ASKER

"COUNT(cmp_tel) AS phone_count" produces errors
"GROUP BY" produces errors

I812

ASKER

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

Tony303

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

I812

ASKER

That statement returns records
some have duplicate phone #s

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial