In a data warehouse environment, we may have to close records when we have new records that are added to the system. Let me first provide the sql.
CASE mut.etl_valid_from
WHEN MAX(mut.etl_valid_from) OVER
(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr))
THEN mut.etl_valid_to
ELSE LEAD(mut.etl_valid_from) OVER
(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr)
ORDER BY mut.etl_valid_from)
END
This is a really nice concise text. Let me show a few items. With the CASE clause, the records are selected that have a maximum for the timestamp when records are entered. The dataset is split according to company code and the gl_account_nr. So for each combination of company code and the gl_account_nr, the maximum of etl_validated_from is calculated. When the etl_valid_from is equal to the maximum, a provided etl_valid_to is inserted into the data base. When etl_valid_from has a lower value, the data from a leading record is taken. That value is the etl_valid_from a leading record that stems from the same bracket of company code and the gl_account_nr that is ordered on etl_valid_from.