Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Only show Data return if there is more than one occurance of the output

Önerilen Yanıt Only show Data return if there is more than one occurance of the output

  • Saturday, February 02, 2013 7:04 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Sunday, February 03, 2013 5:29 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    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 8:03 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    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:40 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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 AM
    Moderator
     
     
    Sign In to Vote
    0

    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