How to find the Continious records based on Key column.
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.
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