SQL - Filter Date Range Per Row in Query Result-CodePudding

I'm trying to figure out the best way to pick a row of member eligibility based on the date of an event.

I have a bunch of members with a particular event in their history and the event has start and end dates associated with it.

MBR_ID	EVT_ID	EVT_START	EVT_TERM
123	HSPC	2020-02-01	2020-12-31
123	HSPC	2021-03-01	2021-12-31

In another table I have their plan dates.

MBR_ID	PLN_ID	EFF_DT	TERM_DT
123	PLAN1	2020-01-01	2020-12-31
123	PLAN2	2021-01-01	2021-12-31

What I would like to do is choose the correct plan from the second table based on EVT_START from the first. So member 123 would have been in PLAN1 for the first HSPC event and in PLAN2 for the second HSPC event.

I'm not concerned if there is crossover where a HSPC event spans two different plans - I want to know the plan the member was in when the HSPC event started. I'm probably overthinking this but I haven't been able to get it yet.

CodePudding user response：

I believe this is what you are looking for.

select *
from events e
inner join plan p on p.MBR_ID = e.MBR_ID 
                     and EVT_START >= EFF_DT
                     and EVT_TERM <= TERM_DT

Fiddle example

CodePudding user response：

Try a subquery:

SELECT
    Events.*,
    (
    SELECT
        PLN_ID
    FROM
        Plans
    WHERE -- plan was active
        MBR_ID = Events.MBR_ID
        AND EFF_DT <= Events.EVT_START
        AND TERM_DT > Events.EVT_START
    ORDER BY
        EFF_DT DESC
    LIMIT 1
    ) AS PLN_ID
FROM
    Events
;

The subquery effectively runs for each row on Events, returning the plan that most recently went into effect, as of that event's start date.

If you're on SQL Server, replace the LIMIT 1 with TOP 1 after SELECT.