Continuous Date Spans

Database

Continuous Date Spans

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:

CUSTOMER_ID SPONSOR LOCATION EFFECTIVE TERMINATION
ABC 123 100B 01/01/2010 03/15/2010
ABC 142 100C 03/01/2010 04/30/2010
ABC 301 300A 06/01/2010 08/15/2010
ABC 425 400B 06/15/2010 06/30/2010
ABC 259 300C 07/15/2010 12/31/2010
ABC 450 400C 10/01/2010 03/01/2011

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.)
1 REPLY

Re: Continuous Date Spans

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';