Post reply

Create view on CDC to display only the changes but not the change for latest values in the table.

  • May 18, 2012 at 12:13 pm

    #258413

    I have to create a view on CDC(change data capture) which only displays the records for the changed data but not the latest data to which it is changed. Suppose value in a row, A is UPDATED to B and again B is UPDATED to C, then I have to display only the records for the change of update(delete+insert - 2 rows) operations but not for the update operation to change it to C, which is the actual data in the table (so no need to have it in view). Please help me, Thanks!

  • May 18, 2012 at 12:39 pm

    #1489893

    couldn't you just add row_number to the results with the PARTITON BY and with DESC, then wrap it to exclude the "first" row (the last change?)

    something like this?

    (untested:)

    SELECT * FROM (

    SELECT

    row_number() over (partition by ModifiedDate ORDER BY ModifiedDate DESC) AS RW,

    *

    FROM cdc.SomeTable

    )

    WHERE RW >1

    ORDER BY RW DESC

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply