Solved

Duplicate phone number report in SSRS

Posted on 2014-01-08
8
317 Views
Last Modified: 2014-01-28
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
0
Comment
Question by:I812
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39766046
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

0
 

Author Comment

by:I812
ID: 39766516
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
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39767322
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
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:I812
ID: 39768499
"COUNT(cmp_tel) AS phone_count"      produces errors
"GROUP BY"      produces errors
0
 

Author Comment

by:I812
ID: 39768544
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
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39770097
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

0
 

Author Comment

by:I812
ID: 39771656
That statement returns records
some have duplicate phone #s
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39774326
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

0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS Access Remotely 5 74
SSRS troubles 4 84
Need return values from a stored procedure 8 45
SSRS Expression syntax with Iif Else 1 37
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question