Results 1 to 3 of 3

Thread: Finding changes

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Finding changes

    Hoping one of you experts can help me - im writing an application and need to run a query that returns only the difference between 2 rows...

    Example:

    -----------------------------------------------------
    SELECT *
    FROM summary, inventory
    WHERE summary.fkInventory = inventory.ID
    AND inventory.ComputerName = '[SOME_NAME_HERE]'
    ------------------------------------------------------

    Now - this will return 2 rows with all columns from my tables. I need to know which columns that are different - and return only those to my application.

    I am a bit new to SQL ( getting there fast ) but i have no clue how to do this - or if it can be done. Otherwise i will have to compare the data in my application but i'd much prefer to let the database do the work if possible...

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    see
    http://www.nigelrivett.net/Triggers_...it_Trails.html

    It has code for logging changes to a column that has changed in a trigger.

    You will have to loop through each column checking the values.

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Finding changes

    Do you have exactly two rows (summeries) per inventory (computer)?

    Do you realize that your query if created as you required returns everytime different columns? You can beter consider to let the query return your columnname, the original value and the changed value.

    First you have to specify your different values:

    SELECT 'YourFirstColumnName' AS ColName,
    cast(O.YourFirstColumnName AS VARCHAR(255)) AS OriginalValue,
    cast(N.YourFirstColumnName AS VARCHAR(255)) AS NewValue,
    fkInventory
    FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
    FROM Summary S
    GROUP BY S.fkInventory ) OldNew
    INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
    WHERE O.YourFirstColumnName <> N.YourFirstColumnName

    UNION

    SELECT 'YourSecondColumnName' AS ColName,
    cast(O.YourSecondColumnName AS VARCHAR(255)) AS OriginalValue, cast(N.YourSecondColumnName AS VARCHAR(255)) AS NewValue,
    fkInventory
    FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
    FROM Summary S
    GROUP BY S.fkInventory ) OldNew
    INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
    WHERE O.YourSecondColumnName <> N.YourSecondColumnName

    UNION

    .....


    You got the idea to repeat the query for every column you want to compare? It's not very elegant, but under the assumptions above, you get what you want.

    Then, you may use this query Q in your query like:

    SELECT ColName, OriginalValue, NewValue
    FROM Q
    WHERE Q.fkInventory = (SELECT ID
    FROM inventory WHERE ComputerName = '[SOME_NAME_HERE]')
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •