none
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

    Saturday, February 02, 2013 7:04 PM

All replies

  • 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

    Sunday, February 03, 2013 5:29 AM
  • 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

    Sunday, February 03, 2013 8:03 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

    Sunday, February 03, 2013 8:40 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

    Tuesday, February 05, 2013 1:25 PM
  • 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

    Thursday, February 07, 2013 2:47 AM