Only show Data return if there is more than one occurance of the output

Question

Hello,

I've trawled the Forum and can't find the answer to this, but it's difficult wording....

I'm building a simple table report in SQL 2.0, I have set all fields and date parameters etc, however I only want the output table to show the results if one of the fields has a duplicate.

For example: The report brings back maybe 500+ rows in a certain date period, a field in those rows would be "Customer ID", I want the output report to only show rows where within the "Customer ID" field there are 2 or more occurrences of the same ID - In essence a report to only show repeat customers.

Is this a possibility, any suggestion would much greatly appreciated!

Thanks

Answer 1

Hi,

     Try to get duplicate values on report using code like shown below .In below example CustomerId 's with 2 or more occurences are displayed.

DECLARE  @Table TABLE(CustomerId INT,DatePeriod DATE)
INSERT INTO @Table VALUES (1,GETDATE())
INSERT INTO @Table VALUES (1,GETDATE())
INSERT INTO @Table VALUES (1,GETDATE())
INSERT INTO @Table VALUES (2,GETDATE())
INSERT INTO @Table VALUES (2,GETDATE())
INSERT INTO @Table VALUES (3,GETDATE())
;WITH Duplicate_Row_cte
     AS (SELECT ROW_NUMBER()OVER(PARTITION BY CustomerId ORDER BY CustomerId) ROW_NUM,*
         FROM   @Table )
SELECT * FROM @Table WHERE CustomerId IN (SELECT CustomerId FROM Duplicate_Row_cte WHERE  ROW_NUM >= 2)

---

Thanks & Regards, sathya

Answer 2

sathya

It can be simpler 

;WITH Duplicate_Row_cte
     AS (SELECT ROW_NUMBER()OVER(PARTITION BY CustomerId ORDER BY CustomerId) ROW_NUM,*
         FROM   @Table )
SELECT * FROM 
Duplicate_Row_cte
 WHERE  ROW_NUM >1

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Hi Uri Dimant,

I gave the code on below assumption:

If the CustomerId = 1 has 2 or more same occurences ,then all the occurence of CustomerId = 1 should be displayed on the report.

---

Thanks & Regards, sathya

Answer 4

I'm fairly new to this, the code you've put above, I've Copied it in the report, but I keep getting an error saying "There is an error on line 0 of custom code: [BC30037] Character is not valid. (rsCompilerErrorInCode)"

Am I doing something silly, or am I missing something.

Apologies again for my basic grasp of this!

Thanks for the replies tho

Answer 5

Hi Markv,

The error related to the custom code, if you had added custom code in the report, please check the code.

Do you specify the dataset query as the query as Sathya post above?  If so, the query should retrieve "CustomerId" which has more than one occurrence.

Regards,
Fanny Liu

---

Fanny Liu
TechNet Community Support