Lets say for example I have the table:
-----------------------------------------------
| id | name | age | hair_color | delete_flag |
-----------------------------------------------
| 1 | Jhon | 20 | black | 0 |
-----------------------------------------------
Now when a property is changed, lets say Jhon is now 21 we insert a new record form Jhon and we set the delete_flag on the previous to 1. It is an odd workflow but I am trying to keep it simple for the example.
My goal is to find out which property of Jhon is changed. In more complicated version of the question is when we have a Person table, Property_type table and Properties table where in the last table looks like :
Properties
-----------------------------------------------------------
| id | person_id | property_type_id | value | delete_flag |
-----------------------------------------------------------
| .... |
-----------------------------------------------------------
Here all props of Jhon are different rows and when Jhon for example changes his hair color all previous props (all rows with person_id = jhons_id) are set to deleted (delete_flag = 1) and all props are added again with only difference: hair color prop is different (only 1 row difference).
Now I want to find out which prop has been changed ? Did he changed his hair color or did he passed 21 years. I tried to do this with a somekind of JOIN between Properties and Properties but did no manage to get the result.
PS: I can't add new columns like 'changed' (0/1).
Try with windows functions lead/lag
Table
ID NAME AGE HAIR_ DELETE_FLAG
---------- ---- ---------- ----- ---------------------------------------
1 John 20 black 1
2 John 21 black 0
select id, name, age, hair_color,
case
when age <> prev_age then 'Age Changed'
when hair_color <> prev_hair_color then 'Hair Changed'
else 'Nothing Changes just Rearranges' end as changes,
prev_age, prev_hair_color
from (
select id, name, age, hair_color,
lead(age) over (partition by name order by id desc) prev_age,
lead(hair_color) over (partition by name order by id desc) prev_hair_color,
delete_flag
from t
) t1
where delete_flag = 0;
OUPUT
ID NAME AGE HAIR_COLOR CHANGES PREV_AGE PREV_HAIR_COLOR
2 John 21 black Age Changed 20 black
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments