Compare previous field values using TSQL

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  July 21, 2010 at 4:37 am

  #223514

  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

• sumit.agrawal

  SSCommitted

  Points: 1616

  More actions

  July 21, 2010 at 4:55 am

  #1196054

  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....

• Praveen Goud Kotha

  Ten Centuries

  Points: 1141

  More actions

  July 21, 2010 at 5:13 am

  #1196060

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

  by fetching top 2 records and compare...

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

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  July 21, 2010 at 5:22 am

  #1196068

  Can you please send me the query example?

  Thanks

• Praveen Goud Kotha

  Ten Centuries

  Points: 1141

  More actions

  July 21, 2010 at 5:35 am

  #1196075

  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]

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  July 21, 2010 at 9:56 pm

  #1196621

  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