Compare previous field values using TSQL

  • Hello,

    I am using SQLServer 2008 R2. I have a requirement to display or highlight

    which field has been modified in the next row compare to the previous row.

    For example, my table & data is like:

    declare @t table

    (

    id int,

    boss_id int,

    managername varchar(10),

    title varchar(20),

    changedate datetime,

    changetime int

    )

    insert into @t values (1,25,'Matt','Manager','1/1/2010',40142);

    insert into @t values (2,26,'Donald','Distrinct Manager','1/1/2010',40142)

    insert into @t values (1,27,'Chris','Manager','1/1/2010',40145);

    insert into @t values (2,25,'Matt','Manager','1/1/2010',40148);

    insert into @t values (2,22,'John','Sales Counselor','1/1/2010',40148);

    insert into @t values (1,24,'Donald 2','District Manager','1/1/2010',40155);

    select * from @t order by id

    There is a difference in boss_id, management and changetime fields for id = 1 and row numbers 1 & 2.

    How to find that what are the fields that have been changed compare to previous row for perticular id?

    Thanks in advance.

    Thanks

  • Did u mean like if there are 3 rows for ID=1, and we are going to insert 1 more row for the same ID, then It should return all the fields where the changes have been done in the previous rows....

    If it is, then you should consider to use cursors....

  • I think..we can achieve without cursors also...

    by fetching top 2 records and compare...

    [font="Comic Sans MS"]Praveen Goud[/font]

  • Can you please send me the query example?

    Thanks

  • Hi..

    Here is my query..

    select id, boss_id, managername, title, changedate, changetime

    from

    (

    select id, boss_id, managername, title, changedate, changetime, row_number() over(partition by id order by boss_id desc) as bt

    from @t

    ) A where A.bt in(1,2)

    which is fetching the latest two records...which are going to be compared..

    i hope ...the rest you can finish..

    [font="Comic Sans MS"]Praveen Goud[/font]

  • Hello,

    I am able to resolve the issue.

    select id, boss_id, managername, title, changedate, changetime, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rownumber

    into #temptable

    from @t order by id

    select * from #temptable

    select * from

    (

    select A.*,

    case when A.managername <> B.managername then 1 else 0 end IsManagerChanged

    , case when A.title <> B.title then 1 else 0 end IsTitleChanged

    from #temptable A inner join

    #temptable B on (A.rownumber = B.rownumber )

    AND A.rownumber = 1

    where a.id = b.id

    UNION ALL

    select A.*,

    case when A.managername <> B.managername then 1 else 0 end IsManagerChanged

    , case when A.title <> B.title then 1 else 0 end IsTitleChanged

    from #temptable A inner join

    #temptable B on (A.rownumber - 1 = B.rownumber )

    where a.id = b.id

    ) A

    order by a.id, a.rownumber

    Result will be like:

    (Result.JPG has been attached)

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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