Solved

Duplicate phone number report in SSRS

Posted on 2014-01-08
8
311 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

15 Experts available now in Live!

Get 1:1 Help Now