I have to get the earliest dt grouped as per id and indicator and then update that in the earliest_dt table
(select min(start_dt) as earliest_dt from test_table group by id,indicator)
in an update statement.
Sample Table Test_table
id start_dt earliest_dt indicator
111 20/02 1
111 15/02 1
111 14/02 1
111 25/02 1
111 23/02 2
111 07/02 2
222 10/02 1
222 22/02 1
222 25/02 1
222 12/02 2
333 22/02 1
333 01/02 1
333 22/02 2
333 05/02 2
Result Table
id start_dt earliest_dt indicator
111 20/02 14/02 1
111 15/02 14/02 1
111 14/02 14/02 1
111 25/02 14/02 1
111 23/02 07/02 2
111 07/02 07/02 2
222 10/02 10/02 1
222 22/02 10/02 1
222 25/02 10/02 1
222 12/02 12/02 2
333 22/02 01/02 1
333 01/02 01/02 1
333 22/02 05/02 2
333 05/02 05/02 2
Use the min window function and optionally an updatable CTE.
with cte as (
select id, start_dt, earliest_dt, indicator
, min(start_dt) over (partition by id, indicator) as new_earliest_dt
from MyTable
)
update cte set earliest_dt = new_earliest_dt;