I am currently working on a requirement that deals with various effective spans for records. The final result should show overall effective spans per CUSTOMER_ID. However, date entries can be split up over multiple entries and locations:
For the final output, the requirement states they want to see the complete effective dates for each customer, something like: CUSTOMER_ID EFFECTIVE TERMINATION ABC 01/01/2010 04/30/2010 ABC 06/01/2010 03/01/2011
A simple MIN/MAX won't work as it is possible for there to be gaps. The effective spans can be sporadic and numerous, as seen above.
Is there a way to group each of the overlapping date spans? (Multiple statements are more than acceptable in the solution.)
I think this does it. There might be a better way, but it seems to work for the data you provided.
SELECT CUSTOMER_ID ,CASE WHEN EFF_ROW_IND = 'E' THEN EFFECTIVE END EFFECTIVE ,CASE WHEN EFF_ROW_IND = 'E' AND TERM_ROW_IND = 'T' THEN TERMINATION ELSE MAX(TERMINATION) OVER (PARTITION BY CUSTOMER_ID ORDER BY EFFECTIVE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) END TERMINATION FROM ( SELECT CUSTOMER_ID ,EFFECTIVE ,TERMINATION ,COALESCE(MIN(TERMINATION) OVER (PARTITION BY CUSTOMER_ID ORDER BY EFFECTIVE, TERMINATION ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), DATE '0001-01-01') PREV_MAX_TERM ,CASE WHEN EFFECTIVE > PREV_MAX_TERM THEN 'E' END EFF_ROW_IND ,COALESCE(MIN(EFFECTIVE) OVER (PARTITION BY CUSTOMER_ID ORDER BY TERMINATION DESC, EFFECTIVE DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), DATE '9999-12-31') PREV_MAX_EFF ,CASE WHEN PREV_MAX_EFF > TERMINATION THEN 'T' END TERM_ROW_IND FROM (SELECT CUSTOMER_ID ,EFFECTIVE ,MAX(TERMINATION) OVER (PARTITION BY CUSTOMER_ID ORDER BY EFFECTIVE ASC ROWS UNBOUNDED PRECEDING) TERMINATION
FROM customer_test) t1 QUALIFY EFF_ROW_IND = 'E' OR TERM_ROW_IND = 'T') t2 QUALIFY EFF_ROW_IND = 'E';