Hi Team,
Request you please help me with the sql that will display old value and new value as desired below.
Scenario1:
Table data as below:
Date Action Test_message
10/01/2019 A SETID equal to USAID
10/01/2019 A AND VENDOR_ID equal to 567843
Output should be as below:
old_value New_value
SETID equal to USAID
AND VENDOR_ID equal to 567843
Scenario2:
Table data as below:
Date Action Test_message
10/01/2019 A SETID equal to USAID
10/01/2019 A AND VENDOR_ID equal to 567843
10/02/2019 A AND STATUS equal to A
10/02/2019 A AND NAME equal to JHON
10/02/2019 A AND ADDRESS equal to NEWYORK
Output should be as below:
old_value New_value
SETID equal to USAID SETID equal to USAID
AND VENDOR_ID equal to 567843 AND VENDOR_ID equal to 567843
AND STATUS equal to A
AND NAME equal to JHON
AND ADDRESS equal to NEWYORK
Scenario3:
Table data as below:
Date Action Test_message
10/01/2019 A SETID equal to USAID
10/01/2019 A AND VENDOR_ID equal to 567843
10/02/2019 A AND STATUS equal to A
10/02/2019 A AND NAME equal to JHON
10/02/2019 A AND ADDRESS equal to NEWYORK
10/03/2019 D AND STATUS equal to A
10/03/2019 D AND NAME equal to JHON
10/03/2019 D AND ADDRESS equal to NEWYORK
10/03/2019 A AND NAME equal to JHON
10/03/2019 A AND ADDRESS equal to NEWYORK
Output should be as below:
old_value New_value
SETID equal to USAID SETID equal to USAID
AND VENDOR_ID equal to 567843 AND VENDOR_ID equal to 567843
AND STATUS equal A
AND NAME equal to JOHN AND NAME equal to JOHN
AND ADDRESS equal to NEWYORK AND ADDRESS equal to NEWYORK
Note: here action A means ADD and D means DELETE.
Whenever I delete any row from the table the next subsequent rows will change their position and hence it will create 2 different rows for the same text_message with different actions. Hence in the above scenario, I deleted 3rd row and hence 4th row become 3rd, and 5th row become 4th row in my original table.
All previous date values always would be under old value column and new value column should have rows newly added.
Thank you.
Regards
Suji