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.
Author |
Topic |
dan0217
Starting Member
3 Posts |
Posted - 2014-06-27 : 23:47:36
|
hi! I have a table history of Employee data.id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUserI 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)
17689 Posts |
Posted - 2014-06-28 : 00:39:33
|
[code]; 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.EmpNamefrom cte h1 inner join cte h2 on h1.EmpNo = h2.EmpNo and h1.rn = h2.rn - 1where h1.EmpName <> h2.EmpNameunion allselect EmpNo = h1.EmpNo, UpdateDate = h2.UpdateDate, UpdateUser = h2.UpdateUser, FieldChanged = 'Mobile', ChangeFrom = h1.Mobile, ChangeTo = h2.Mobilefrom cte h1 inner join cte h2 on h1.EmpNo = h2.EmpNo and h1.rn = h2.rn - 1where h1.Mobile <> h2.Mobileunion all. . . [/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
dan0217
Starting Member
3 Posts |
Posted - 2014-06-28 : 01:39:09
|
thanks for helping!but there is an error, it says"Conversion failed when converting the varchar value 'Nino' to data type int." |
|
|
sardam98
Starting Member
10 Posts |
|
dan0217
Starting Member
3 Posts |
Posted - 2015-06-13 : 14:36:13
|
Good day!how can i do it with sys.columns? thanks |
|
|
|
|
|
|
|