How to find the Continious records based on Key column.

Database

How to find the Continious records based on Key column.

Hi Guru,

In my project i have a requirement where if a member has more than one records then i need to find out the gap records and load it to target, if a member has three or four records but he is not having gap in effecting date then i need to pull only one records, if gap is there between effective data then i need to pull two or three records depend upon the years of gap.

Below is the Source example.

CNSMR_ID MBR_KEY MBR_PROD_ENRLMNT_TRMNTN_DT MBR_PROD_ENRLMNT_EFCTV_DT
100 KEY1 1/1/2001 12/31/8888
102 KEY2 1/1/2001 12/31/2006
102 KEY3 1/1/2007 12/31/8888
103 KEY4 1/1/2001 12/31/2006
103 KEY4 1/1/2008 12/31/8888
104 KEY5 1/1/2001 12/31/2006
104 KEY5 1/1/2008 12/31/8888
104 KEY6 1/1/2007 12/31/8888
105 KEY7 1/1/2001 12/31/2006
105 KEY7 1/1/2008 12/31/8888
105 KEY8 1/1/2008 12/31/8888

target i need as Flow:-

CNSMR_ID MBR_PROD_ENRLMNT_TRMNTN_DT MBR_PROD_ENRLMNT_EFCTV_DT
100 1/1/2001 12/31/8888
102 1/1/2001 12/31/8888
103 1/1/2001 12/31/2006
103 1/1/2008 12/31/8888
104 1/1/2001 12/31/8888
105 1/2/2001 12/31/2006
105 1/1/2008 12/31/8888

Any help will be appreciated.

Thanks,
Amit

3 REPLIES

Re: How to find the Continious records based on Key column.

Please help
Senior Apprentice

Re: How to find the Continious records based on Key column.

Hi Amit,
looks like you don't want gaps, you want to densify the data:

SELECT grp, MIN(start_dt), MAX(end_dt)
FROM
(
SELECT
grp, start_dt, end_dt,
SUM(flag) OVER (PARTITION BY grp ORDER BY start_dt, end_dt ROWS UNBOUNDED PRECEDING) AS dummy_grp
FROM
(
SELECT grp, start_dt, end_dt,
CASE WHEN start_dt
- MIN(end_dt) OVER
(PARTITION BY grp ORDER BY start_dt, end_dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) > 1 THEN 1 ELSE 0 END AS flag
FROM (SELECT DISTINCT CNSMR_ID AS grp, MBR_PROD_ENRLMNT_TRMNTN_DT AS start_dt, MBR_PROD_ENRLMNT_EFCTV_DT AS end_dt FROM tab) AS dt
) AS dt
) AS dt
GROUP BY grp, dummy_grp
ORDER BY 1,2

Dieter

Re: How to find the Continious records based on Key column.

Thanks a lot.It was really useful. :-)