Post reply

Compare 2 rows, find the column changed, need extra column date

  • March 2, 2019 at 11:43 pm

    #409563

    f

  • March 4, 2019 at 8:19 pm

    #2023524

    You could possibly get it more efficient by using a single CTE that numbers the rows, much like your  #unpivot_step2 CTE does, then join to itself, based on reqid and row number joined to row number + 1
    select A.*, B.* from #unpivot_step2 CTE A   -- you will need to name every column differently, ie A.name as 'AName', B.Name as 'BName' otherwise you can't do the next step. don't use *
    JOIN #unpivot_step2 CTE B
    on A.reqid = B.reqid  -- you seem to change names to seqno?
    AND A.RowNum = B.RowNum + 1

    This returns every row and it's successive row for a given reqid/seqno, in a single row of data. You then only need to read through this result set and identify the column that changed as you will have the from & to dates in the row. You could probably do this with a single select and case statements. I'll do some testing and see if I can get a code sample for you.

    I'm not sure how efficient this is, but it's worth trying.

    Leo
    Nothing in life is ever so complicated that with a little effort it cant be made more complicated.!

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

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