-1

Please explain the best way to compare two rows and display the fields along with the change value.

Example

ID EmployeeID EmployeeName Address1 Address2 Salary UpdatedDate
1 501 Johnson 2nd Ave 108 850 04/04/2020
2 501 Johnson 2nd Ave 109 900 04/05/2020
3 501 Johnson 3rd Ave 109 950 04/06/2020

Each row is an new version of the previous row. Based on the input value, the previous row need to be compared and difference should be highlighted.

Example: Input: 3

Column NewValue OldValue
Address1 3rd Ave 2nd Ave
Salary 950 900

Input: 2

Column NewValue OldValue
Address2 109 108
Salary 900 850

The fields names should dynamically picked while comparing and should ignore the primary key and updateddate fields alone.

Thanks

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Jack A
  • 7
  • 1
  • All that has been posted is a program description. However, we need you to ask a question according to the [ask] page. We can't be sure what you want from us. Please [edit] your post to include a valid question that we can answer. Reminder: make sure you know what is on-topic here by visiting the [help/on-topic]; asking us to write the program for you, suggestions, and external links are off-topic. – gunr2171 Apr 15 '21 at 17:16
  • Please show the code you currently have so we can see where the problem is. – Rufus L Apr 15 '21 at 17:39
  • Could you create a trigger on that table and log the changes in a separate table like [THIS](https://stackoverflow.com/questions/19737723/log-record-changes-in-sql-server-in-an-audit-table)? – WAMLeslie Apr 15 '21 at 17:39
  • what you have tried so far in this context ? – dotnetstep Apr 15 '21 at 17:44
  • Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. How do plan to handle different data types, e.g. `NewValue` and `OldValue` might be `money` or `nvarchar(max)`? Is `ID` guaranteed to be unique and dense, i.e. start at `1` and count _nicely_? What is The Answer for input `1`? – HABO Apr 15 '21 at 17:44
  • @WAMLeslie We did not want to log in a table and preferred to compute the difference only when required. Thanks a lot for providing reference. – Jack A Apr 17 '21 at 14:37

1 Answers1

0

Here is an option that will dynamically unpivot your data via a bit a JSON.

Example or dbFiddle

;with cte as ( 
Select * 
      ,OldValue = lag(Value,1) over (partition by EmployeeID,[Key] order by UpdatedDate)
 From  YourTable A
 Cross Apply (
                Select [Key]
                      ,Value
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                 Where [Key] not in ('ID','UpdatedDate')
             ) B
)
Select [EmployeeID]
      ,[Column]=[Key]
      ,[NewValue] = [Value]
      ,[OldValue]
 From  cte
 Where [Value]<>[OldValue]
 Order By UpdatedDate desc

Returns

EmployeeID  Column      NewValue    OldValue
501         Address1    3rd Ave     2nd Ave
501         Salary      950         900
501         Address2    109         108
501         Salary      900         850
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66