Solved

Duplicate phone number report in SSRS

Posted on 2014-01-08
8
313 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
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 40

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2008 Preview tab - Parameter window sizing 2 93
source and target row counts 4 26
sql 2012 cluster  SSRS cluster aware 2 29
SSRS Enable Remote Errors 4 25
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 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