Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare previous field values using TSQL Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 4:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:02 AM
Points: 1,020, Visits: 1,290
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
Post #956228
Posted Wednesday, July 21, 2010 4:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 14, 2011 12:59 PM
Points: 73, Visits: 209
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....
Post #956235
Posted Wednesday, July 21, 2010 5:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 16, 2010 7:13 AM
Points: 315, Visits: 93
I think..we can achieve without cursors also...

by fetching top 2 records and compare...


Praveen Goud
Post #956242
Posted Wednesday, July 21, 2010 5:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:02 AM
Points: 1,020, Visits: 1,290
Can you please send me the query example?

Thanks
Post #956251
Posted Wednesday, July 21, 2010 5:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 16, 2010 7:13 AM
Points: 315, Visits: 93
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..


Praveen Goud
Post #956259
Posted Wednesday, July 21, 2010 9:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:02 AM
Points: 1,020, Visits: 1,290
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

  Post Attachments 
Result.JPG (13 views, 36.58 KB)
Post #956914
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse