Solved

Duplicate phone number report in SSRS

Posted on 2014-01-08
8
312 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
 

Author Comment

by:I812
ID: 39768499
"COUNT(cmp_tel) AS phone_count"      produces errors
"GROUP BY"      produces errors
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now