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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 show changes from and changes to in a history tabl
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dan0217
Starting Member

3 Posts

Posted - 06/27/2014 :  23:47:36  Show Profile  Reply with Quote
hi! I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/28/2014 :  00:39:33  Show Profile  Reply with Quote

; with cte as
(
	select	*, rn = row_number() over (partition by EmpNo order by id)
	from	history
)
select	EmpNo		= h1.EmpNo,
	UpdateDate	= h2.UpdateDate,
	UpdateUser	= h2.UpdateUser,
	FieldChanged	= 'EmpName',
	ChangeFrom	= h1.EmpName,
	ChangeTo	= h2.EmpName
from	cte h1
	inner join cte h2	on	h1.EmpNo	= h2.EmpNo
				and	h1.rn		= h2.rn - 1
where	h1.EmpName	<> h2.EmpName
union all
select	EmpNo		= h1.EmpNo,
	UpdateDate	= h2.UpdateDate,
	UpdateUser	= h2.UpdateUser,
	FieldChanged	= 'Mobile',
	ChangeFrom	= h1.Mobile,
	ChangeTo	= h2.Mobile
from	cte h1
	inner join cte h2	on	h1.EmpNo	= h2.EmpNo
				and	h1.rn		= h2.rn - 1
where	h1.Mobile	<> h2.Mobile

union all

. . . 



KH
Time is always against us

Go to Top of Page

dan0217
Starting Member

3 Posts

Posted - 06/28/2014 :  01:39:09  Show Profile  Reply with Quote
thanks for helping!
but there is an error, it says

"Conversion failed when converting the varchar value 'Nino' to data type int."
Go to Top of Page

sardam98
Starting Member

Iraq
10 Posts

Posted - 06/29/2014 :  03:36:17  Show Profile  Reply with Quote
thanks



http://www.soran.edu.iq

http://www.soran.edu.iq
Go to Top of Page

dan0217
Starting Member

3 Posts

Posted - 06/13/2015 :  14:36:13  Show Profile  Reply with Quote
Good day!

how can i do it with sys.columns? thanks
Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000