Saturday, February 02, 2013 7:04 PM
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!
Sunday, February 03, 2013 5:29 AM
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
- Proposed As Answer by Fanny LiuMicrosoft Contingent Staff, Moderator Thursday, February 07, 2013 2:40 AM
Sunday, February 03, 2013 8:03 AM
It can be simpler
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
- Proposed As Answer by Rahul Kumar (Rahul Vairagi) Sunday, February 03, 2013 10:28 AM
Sunday, February 03, 2013 8:40 AM
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
Tuesday, February 05, 2013 1:25 PM
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
Thursday, February 07, 2013 2:47 AMModerator
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.
TechNet Community Support