I have a member_coverage table with coverage records for each member, showing the date ranges when their coverage was active. When I left outer join it, I get a lot of duplicate rows per member. I'm trying to figure out how to check if a member had continuous coverage—219 days before and 60 days after their delivery date—without any gaps.
PAT_ID, APPT_DATE, and DELIVERY_DATE remains unchanged, but left outer join member_coverage on pat_id creates more than one row per member.
Here's an example member and their coverage dates; null indicates their coverage had not ended and is currently active:
| PAT_ID | APPT_DATE | DELIVERY_DATE | MEM_EFF_FROM_DATE | MEM_EFF_TO_DATE |
|---|---|---|---|---|
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2004-03-01 00:00:00 | 2008-12-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2011-01-01 00:00:00 | 2013-05-19 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2013-05-20 00:00:00 | 2015-10-31 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2016-06-01 00:00:00 | 2016-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2016-10-01 00:00:00 | 2019-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2017-06-16 00:00:00 | 2017-08-24 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2019-10-01 00:00:00 | 2021-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2021-10-01 00:00:00 | 2023-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2023-10-01 00:00:00 | 2024-03-31 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2024-05-01 00:00:00 | 2024-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2024-10-01 00:00:00 | |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2021-05-01 00:00:00 | 2021-05-31 00:00:00 |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2021-06-01 00:00:00 | 2023-12-31 00:00:00 |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2024-02-01 00:00:00 |
I have a member_coverage table with coverage records for each member, showing the date ranges when their coverage was active. When I left outer join it, I get a lot of duplicate rows per member. I'm trying to figure out how to check if a member had continuous coverage—219 days before and 60 days after their delivery date—without any gaps.
PAT_ID, APPT_DATE, and DELIVERY_DATE remains unchanged, but left outer join member_coverage on pat_id creates more than one row per member.
Here's an example member and their coverage dates; null indicates their coverage had not ended and is currently active:
| PAT_ID | APPT_DATE | DELIVERY_DATE | MEM_EFF_FROM_DATE | MEM_EFF_TO_DATE |
|---|---|---|---|---|
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2004-03-01 00:00:00 | 2008-12-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2011-01-01 00:00:00 | 2013-05-19 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2013-05-20 00:00:00 | 2015-10-31 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2016-06-01 00:00:00 | 2016-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2016-10-01 00:00:00 | 2019-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2017-06-16 00:00:00 | 2017-08-24 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2019-10-01 00:00:00 | 2021-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2021-10-01 00:00:00 | 2023-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2023-10-01 00:00:00 | 2024-03-31 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2024-05-01 00:00:00 | 2024-09-30 00:00:00 |
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | 2024-10-01 00:00:00 | |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2021-05-01 00:00:00 | 2021-05-31 00:00:00 |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2021-06-01 00:00:00 | 2023-12-31 00:00:00 |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | 2024-02-01 00:00:00 |
I'm hoping for the following output:
| PAT_ID | APPT_DATE | DELIVERY_DATE | COVERAGE_TYPE |
|---|---|---|---|
| 123456790 | 2023-05-16 00:00:00 | 2023-12-30 00:00:00 | No Gap |
| 098765432 | 2023-12-18 00:00:00 | 2024-06-11 00:00:00 | Gap |
098765432 had a gap (no coverage) from 1/1/24 to 1/31/24, with coverage resuming 2/1/24
It's not clear if you are using ORACLE DB or SQLDeveloper to connect to another vendor DB, but with Oracle you can us math_recognize:
WITH member_coverage(pat_id, appt_date, delivery_date, mem_eff_from_date, mem_eff_to_date) AS (
select 123456790, date '2023-05-16', date '2023-12-30', date '2004-03-01', date '2008-12-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2011-01-01', date '2013-05-19' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2013-05-20', date '2015-10-31' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2016-06-01', date '2016-09-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2016-10-01', date '2019-09-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2017-06-16', date '2017-08-24' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2019-10-01', date '2021-09-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2021-10-01', date '2023-09-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2023-10-01', date '2024-03-31' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2024-05-01', date '2024-09-30' from dual union all
select 123456790, date '2023-05-16', date '2023-12-30', date '2024-10-01', null from dual union all
select 098765432, date '2023-12-18', date '2024-06-11', date '2021-05-01', date '2021-05-31' from dual union all
select 098765432, date '2023-12-18', date '2024-06-11', date '2021-06-01', date '2023-12-31' from dual union all
select 098765432, date '2023-12-18', date '2024-06-11', date '2024-02-01', null from dual
)
SELECT d.*, delivery_date - 219 AS start_range, delivery_date + 60 AS end_range FROM (
SELECT * FROM member_coverage
MATCH_RECOGNIZE (
PARTITION BY pat_id, appt_date, delivery_date
ORDER BY mem_eff_from_date, mem_eff_to_date nulls last
MEASURES
MAX(nvl(mem_eff_to_date+1, date '9999-12-31')) AS gap_from,
NEXT(mem_eff_from_date) - 1 AS gap_to
ALL ROWS PER MATCH
PATTERN( ( gap | {-no_gap-} ) )
DEFINE
gap AS MAX(nvl(mem_eff_to_date, date '9999-12-31')) < NEXT(mem_eff_from_date-1)
)
) d
WHERE LEAST(delivery_date + 60, gap_to) >= GREATEST(delivery_date - 219, gap_from )
;
Up to you to adjust the +/-1 and the "<", ">=" conditions to match your edge cases with better/more complete test data. I left the range dates for better visualisation.
PAT_ID APPT_DATE DELIVERY_D MEM_EFF_FR MEM_EFF_TO GAP_FROM GAP_TO START_RANG END_RANGE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
98765432 2023-12-18 2024-06-11 2021-06-01 2023-12-31 2024-01-01 2024-01-31 2023-11-05 2024-08-10