Compare two rows of log table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare two rows of log table

Author

Topic

sqlps
Starting Member

8 Posts

Posted - 2013-10-19 : 01:29:22

Hi All.
I have one table to keep log of another table. It has following structure

ID version status date transaction transby
-- -------- ----- ---- ----------- --------
1 1 a 1/1/2013 1000 srp
2 1 a 1/1/2013 2000 mab
1 2 u 1/1/2013 1001 mab

i want a result in varchar format if pass ID = 1 and version = 2
like
" version : has been changed 1 to 2 and Transaction : has been changed 1000 to 1001 and transby : has been changed srp to mab"

Please guide me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-19 : 02:27:38

[code]
select "verson : has been changed " + l1.version + ' to ' + l2.version
+ ' and Transaction : has been changed ' + l1.transaction + ' to ' + l2.transaction
+ ' and transby : has been changed ' + l1.transby + ' to ' + l2.transby
from log_table l1
inner join log_table l2 on l1.ID = l2.ID
where l1.ID = @id
and l1.version = @ver - 1
and l2.version = @ver
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqlps
Starting Member

8 Posts

Posted - 2013-10-19 : 04:41:00

Thank you Khtan for your valueble reply. But I want result to compare all columns of both rows if any difference is there then it convert to into string as explained in above. So I want to know value changed columns.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2013-10-19 : 18:01:22

Select the table with conditions on id and version, then assign new columns, then compare the columns to get message to return.

It is pretty easy, just time consuming. If you want detail, let me know.

sqlps
Starting Member

8 Posts

Posted - 2013-10-20 : 01:37:16

thank you namman, Please give the details

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-20 : 05:40:23

see the changes in red as namman has suggested


select "verson : has been changed "             + l1.version     + ' to ' + l2.version 
       + case when l1.transaction  <> l2.transaction then 
                   ' and Transaction : has been changed ' + l1.transaction + ' to ' + l2.transaction 
              else ''
              end
       + ' and transby : has been changed '     + l1.transby     + ' to ' + l2.transby
from   log_table l1
       inner join log_table l2 on l1.ID = l2.ID
where  l1.ID      = @id
and    l1.version = @ver - 1
and    l2.version = @ver

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources