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
Share Improve this question edited Feb 1 at 10:36 MT0 169k12 gold badges66 silver badges127 bronze badges asked Feb 1 at 3:09 HusnaHusna 394 bronze badges1 Answer
Reset to default 1It'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