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
