Help with condensing a history table

[csh8428]

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]. 

To put it more concisely: For any given [KEY_c] and [MTN_STATUS_IND], condense the data it 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].

I've tried various combinations of Tiling, multi-row formulas, and summarizations, but I can't quite get it to work perfectly. 

I've attached a sample data set and results as well as as a screenshot to demonstrate what I'm trying to achieve.

Thanks for any help!!

-Craig

[alexnajm]

I think you are right there with a solution - here's what I'd do:

- Summarize tool with min [EFF_DT] and the [Key_c] as a grouping

- a separate Summarize tool with max [EXP_DT] and the [Key_c] as a grouping

- Join the two streams on the [Key_c]

[nagakavyasri · Accepted solution]

@csh8428 Use multi row formula and summarize tool