Блог

Хотя я проверил другие сообщения о перекрывающихся диапазонах дат, я не нашел ни одного для этой проблемы.

У меня есть две таблицы, одна для истории Attribute_spans участника

 MEMBERCODE |   START_DATE   |   END_DATE    | ATTRIBUTE

423223     |   10/01/2020   |  12/31/9999   | Dual
423223     |   11/01/2020   |  11/30/2020   | Cost Share
 

Затем есть еще одна таблица для истории членства каждого участника или периода регистрации.

 MEMBERCODE |   EFFECTIVE_DATE |  TERM_DATE  |  PLAN_PRODUCT

423223     |   11/01/2020     |  12/31/9999 |  Foo
423223     |   09/01/2020     |  09/30/2020 |  Bar
 

Самый текущий диапазон атрибутов участника всегда должен находиться в пределах самого последнего диапазона регистрации участника, но на каждый диапазон регистрации может быть более одного атрибута.

Проблема в том, что я хочу найти все диапазоны атрибутов из истории участника, которые не полностью лежат в пределах КАКОГО-ЛИБО диапазона регистрации из их истории.
Например, для этого атрибута Span:
10/1/2020 12/31/9999 ** в любых диапазонах регистрации для этого участника нет периода 10/1. Он попадает в промежуток времени.

 SELECT a.MEMBER_NBR, a.START_DATE, A.END_DATE,B.EFFECTIVE_DATE ,B.TERM_DATE,
A.ATTRIBUTE,B.PLAN_PRODUCT
FROM Attribute_Spans A
JOIN Enrollment_Spans B ON A.MemberCode = B.MemberCode
WHERE ((A.Start_Date NOT BETWEEN B.Effective_Date and B.End_Date) AND (A.End_date NOT BETWEEN 
B.Effective_Date and B.End_Date))
AND A.MemberCode = B.MemberCode
 

Это не работает, потому что он находит диапазоны дат атрибутов, которые не попадают в диапазон диапазона регистрации, но этот диапазон атрибутов попадает в другой диапазон диапазона регистрации для этого конкретного участника

Что я получаю

 MEMBER_CODE | START_DATE | END_DATE   | EFFECTIVE_DATE | TERM_DATE  | ATTRIBUTE | PLAN_PRODUCT
423223      | 10/01/2020 | 12/31/2020 | 09/01/2020     | 09/30/2020 | Dual      | Bar
423223      | 10/01/2020 | 12/31/2020 | 11/01/2020     | 12/31/2020 | Dual      | Foo
423223      | 11/01/2020 | 11/30/2020 | 09/01/2020     | 09/30/2020 | Cost Share| Bar
 

Чего я ожидаю

 MEMBER_CODE | START_DATE | END_DATE   | EFFECTIVE_DATE | TERM_DATE  | ATTRIBUTE | PLAN_PRODUCT
423223      | 10/01/2020 | 12/31/2020 | 09/01/2020     | 09/30/2020 | Dual      | Bar
423223      | 10/01/2020 | 12/31/2020 | 11/01/2020     | 12/31/2020 | Dual      | Foo
 

Некоторый начальный код для создания таблиц

 CREATE TABLE Attribute_Spans (
MemberCode int,
StartDate date,
EndDate date,
Attribute char(50)
)
INSERT INTO Attribute_Spans (MemberCode,StartDate,EndDate,Attribute)
VALUES (423223,'10/01/2020','12/31/9999','Dual'),
        (423223,'11/01/2020','11/30/2020','Cost Share')

CREATE TABLE Enrollment_Spans (
    MemberCode int,
    Effective_Date date,
    End_Date date,
    PlanProduct char(50)
    )
INSERT INTO Enrollment_Spans (MemberCode,Effective_Date,End_Date,Plan_Product)
VALUES (423223,'09/01/2020','09/30/2020','Foo'),
        (423223,'11/01/2020','12/30/2020','Bar')
 

Используйте not exists :

 select a.*
from attribute_spans a
where not exists (select 1
                  from Enrollment_Spans es
                  where es.membercode = a.membercode and
                        a.end_date > es.effective_date and
                        a.start_date < es.end_date 
                 );
 

Два периода времени перекрываются, если один начинается до окончания другого. И один заканчивается после начала другого.

Это не рассматривает окончание / начало в ту же дату как совпадение. Если вы хотите, чтобы это учитывалось, измените > and < на >= and <= .