I am trying to update my Dimention table during the load process with Last_Trans_Flag set to 'A' -active or 'I' -inactive. The Last_Trans_Flag for the new record being inserted is set to 'A' and the previous records for the customer are set to 'I'.

During the initial load of the SCD, the first occurrence of each customer will be set an 'I' flag and the most recent
customer with max(effective_date) will be set to 'A' .

I need the query to accomplish this. Thanks!