none
Show value diffs for conversion RRS feed

  • Question

  •  
     I'm moving some old load(DTS) sql2005 processes to Sql2012. During this process I would like to compare tables for any
    diffs. I have this SP that uses a field value called Source to differentiate my Prod data from Testing. The SP works but lists
    diffs even though when I query my merge table I don't see any fields not matching. Can this SP be changed to exclude the
    rounding issues?

     If data within .01 or .001 or .0001 for the DATA Types defined than consider that a MATCH in the SP results and don't show an exception. I only want to see non rounding exceptions.

      
      
    DECLARE @CrosstabColumns varchar(max)
    
    SET @CrosstabColumns = STUFF((SELECT ',SUM(CASE WHEN source = ''kpi'' THEN ' + COLUMN_NAME + ' ELSE -' + COLUMN_NAME + ' END) AS [Diff_' + COLUMN_NAME + ']'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'INV_LINE_ITEM_Merge'
    AND DATA_TYPE IN ('int','decimal','money','float')
    FOR XML PATH('') ),1,1,'')
    
    
    DECLARE @SQL varchar(max)
    
    SET @SQL = 'SELECT INVOICE_NBR,PRIME_LINE_NBR,''kpi'' AS Source,' + @CrossTabColumns + '
    FROM INV_LINE_ITEM_Merge
    GROUP BY INVOICE_NBR,PRIME_LINE_NBR
    order by 1'
    
    EXEC (@SQL)

     Thanks.
     
    Friday, July 19, 2019 2:33 PM

All replies


  • To start with, I can't see that the generated SQL is doing any filtering at all.

    Have you thought of adding
    PRINT  @SQL

    and tried to figure out how you would like to change the query?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 19, 2019 9:51 PM
  • HI,hart60

    Add script example data and your table.

    Monday, July 22, 2019 7:36 AM
  • Hi hart60,

    Would you mind providing any sample data as well as your desire output? 

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 22, 2019 9:06 AM
  • Hi hart60

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 7:27 AM