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!
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
- Proposed as answer by Rahul Kumar (Rahul Vairagi) Sunday, February 03, 2013 10:28 AM
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
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