I have a history table that tracks all sorts of changes to different fields. I only need to analyze the changes to [MTN_STATUS_IND]. Because it's a history table that tracks a lot of changes there will be multiple rows with the same [MTN_STATUS_IND], but with different date ranges. So the [EFF_DT] and [EXP_DT] need to be "condensed" to 1 record for every given [MTN_STATUS_IND] and [KEY_c].
To put it more concisely: For any given [KEY_c] and [MTN_STATUS_IND], condense the data to 1 record with the min [EFF_DT] and max [EXP_DT] while maintaining the correct chronological order and the changes that occur in [MTN_STATUS_IND].
Example Thanks for any help!
-C
Here's a screenshot of an example.
This is easy to do with Teradata's NORMALIZE feature. That requires a PERIOD data type instead of separate DATE fields, but it's not hard to convert:
WITH normalized_using_period as
(SELECT NORMALIZE KEY_c, MTN_STATUS_IND,
-- PERIOD does not include ending bound so adjust "closed" periods
PERIOD(EFF_DT,CASE WHEN EXP_DT < date'9999-12-31' THEN NEXT(EXP_DT) ELSE EXP_DT END) as PD
FROM my_history_table
)
-- The hard part is done, just split PERIOD and re-adjust
SELECT KEY_c, MTN_STATUS_IND,
BEGIN(PD) as EFF_DT,
CASE WHEN END(PD) < DATE'9999-12-31' THEN PRIOR(END(PD)) ELSE END(PD) END as EXP_DT
FROM normalized_using_period
ORDER BY KEY_c, EFF_DT DESC;