如何验证会员分娩日前后（219天前/60天后）保险覆盖无中断？
2026-6-14
验证会员分娩前后特定时间段的保险覆盖连续性
我有一张member_coverage表，记录每位会员的保险生效日期范围（MEM_EFF_FROM_DATE至MEM_EFF_TO_DATE，空值表示保险仍生效）。按PAT_ID左外连接该表后，每个会员会生成大量重复行。我需要验证会员在分娩日（DELIVERY_DATE）前219天至后60天的时间段内，保险覆盖是否连续无中断。输出需保留PAT_ID、APPT_DATE和DELIVERY_DATE字段值，同时新增字段标识覆盖状态。
示例数据
| 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 |
期望输出
| 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在2024年1月1日至1月31日期间存在保险覆盖中断，2月1日恢复保险。
内容的提问来源于stack exchange，提问作者Husna
相关产品推荐
相关产品推荐
