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
 General SQL Server Forums
 New to SQL Server Programming
 show changes from and changes to in a history tabl

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 | 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)

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.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

. . .
[/code]


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

Go to Top of Page

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."
Go to Top of Page

sardam98
Starting Member

10 Posts

Posted - 2014-06-29 : 03:36:17
thanks



http://www.soran.edu.iq

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

dan0217
Starting Member

3 Posts

Posted - 2015-06-13 : 14:36:13
Good day!

how can i do it with sys.columns? thanks
Go to Top of Page
   

- Advertisement -