I've a table in my database with record of an agent. The record looks like
Here, Site ID is a place where the Agent 1001 visits at logs himself in at TimeIn and then logs out of that site at TimeOut and raises some tickets and its corresponding amount. Now, If the site is not changed then I want to aggregate these records as shown in the table given below:
I am using MySQL database to do this. Using window functions I can find out consecutive matching Site ID and then mark it as some flag_variable (let's say 1) in a new column. Now, for these rows where flag=1, I want to update current row TimeOut = next row Timeout. For three or more consecutive record of same Site ID this approach will not work, then we should need a recursive approach I guess. My question is how can we do this update in the current table and remove redundant records in the table using MySQL syntax.