Condensing history table for teradata SQL

Question

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.

Answer 1

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;