SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Compare two rows of log table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlps
Starting Member

India
8 Posts

Posted - 10/19/2013 :  01:29:22  Show Profile  Reply with Quote
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

Edited by - sqlps on 10/19/2013 01:39:39

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 10/19/2013 :  02:27:38  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

sqlps
Starting Member

India
8 Posts

Posted - 10/19/2013 :  04:41:00  Show Profile  Reply with Quote
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.
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
272 Posts

Posted - 10/19/2013 :  18:01:22  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlps
Starting Member

India
8 Posts

Posted - 10/20/2013 :  01:37:16  Show Profile  Reply with Quote
thank you namman, Please give the details
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 10/20/2013 :  05:40:23  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000