Hello Team,
version i am working is 11g.
Below is sample data
select '123' cus,'1' code,'NULL' flag,'somn@gmail.com' email,'12-DEC-19' old_date from dual union
select '456' cus,'2' code,'Y' flag,'somn@gmail.com' email,'17-DEC-19' old_date from dual union
select '567' cus,'3' code,'NULL' flag,'somn@gmail.com' email,'16-DEC-19' old_date from dual union
select '789' cus,'4' code,'Y' flag,'somn@gmail.com' email,'13-DEC-19' old_date from dual union
i need to define new field called latest_date to above sql , which should be updated only if any change in flag value is observed for the customer on the old_date.
for eg cus 567 with NULL flag on 16 DEC changed to Y on 17 DEC then latest_date should be updated as 17 DEC for all rows and if there is no change in flag then the latest_date feild should take same value as old date.
Thanks.