How to tell in SQL Partition if plan was renewed without lapse?

Question

I have the following data:

PATIENT|CLIENT |START_DATE  |END_DATE  |CHECKLIST  |PLAN     
19993   7499    2017-08-18  2018-08-19  1           ZFD
19994   5994    2017-04-17  2018-04-18  1           ZRLZG
19994   5994    2017-04-17  2018-04-18  2           ZRLZG
19994   5994    2017-06-14  2018-06-15  1           ZRLZG
19994   5994    2017-06-14  2018-06-15  2           ZRLZG
19994   5994    2018-05-31  2018-06-02  1           CZMRZDCB
19994   5994    2018-05-31  2019-06-01  1           ZRLZG
19994   5994    2018-05-31  2019-06-01  2           ZRLZG

How can I add a new boolean column that tells me if a plan, for a given patient/client combination, is renewed? By renewed I specifically mean, for a given Row A, if there is any other Row B with the same patient/client/plan that has a Row B Start to Row B End that overlaps with Row A's End.

In this case, the desired output would be:

PATIENT|CLIENT |START_DATE  |END_DATE  |CHECKLIST  |PLAN     |RENEWED
19993   7499    2017-08-18  2018-08-19  1           ZFD       FALSE
19994   5994    2017-04-17  2018-04-18  1           ZRLZG     TRUE
19994   5994    2017-04-17  2018-04-18  2           ZRLZG     TRUE
19994   5994    2017-06-14  2018-06-15  1           ZRLZG     TRUE
19994   5994    2017-06-14  2018-06-15  2           ZRLZG     TRUE
19994   5994    2018-05-31  2018-06-02  1           CZMRZDCB  FALSE
19994   5994    2018-05-31  2019-06-01  1           ZRLZG     FALSE
19994   5994    2018-05-31  2019-06-01  2           ZRLZG     FALSE

Hoping to do this in one Partition command if possible.

Update: Seeing the proposed solutions I realize I oversimplified the situation by not showing the field CHECKLIST which poses an issue to lead/lag. I still only care about renewed plans and not anything w/r/t checklist, but using lead/lag could compare to another checklist in the same plan which I want to avoid.

Gordon Linoff · Answer 1 · 2021-04-25T23:52:38.957

0

You can use window functions:

select t.*,
       (case when lag(end) over (partition by patient, client, plan order by start) >= end or
                  lead(start) over (partition by patient, client, plan order by start) <= end
             then true else false
        end) as renewed
from t;

The logic checks if the previous end for the same patient/client/plan overlaps with the current start or if the next start overlaps. Note: This uses a case because the lag()/lead() could return NULL.

edited Apr 25 '21 at 23:52

answered Apr 25 '21 at 23:35

Gordon Linoff

1,242,037
58
646
786

you will want an `or` between the LAG and LEAD logic. at which point using a CASE to evaluate a true/false logic, to generate a true/false value, can just be clasped to "the logic is the result". – Simeon Pilgrim Apr 25 '21 at 23:46
@MarkMcGown . . . If your column is really called `end` that that will cause problems. – Gordon Linoff Apr 25 '21 at 23:54

score 0 · Answer 2 · answered Apr 25 '21 at 23:55

depending on the question you are trying to answer here is some SQL that tries answers both

select t.*
    ,lag(end) over (partition by patient, client, plan order by start) as prior_end
    ,lag(start) over (partition by patient, client, plan order by start) as prior_start
    ,lead(start) over (partition by patient, client, plan order by start) as next_start
    ,lead(end) over (partition by patient, client, plan order by start) as next_end
    --- was the prior renewed
    ,prior_end >= start AND prior_start < start as prior_was_overlapped_so_prior_was_renewed
    -- this was renewed by next
    ,next_start <= end and next_end > end as next_was_overlapped_so_next_was_renewed
from t;

the way to think about these things is to draw to "overlapping" character ranges, A-C an B-D, the 2nd renews the prior as B is on/before C, but you dont want to count a row that is A-B as renewing the first, as it actually ends before the first finishes, so you need to check for that also.

There are the other time range question when you are trying to find overlaps, like A-F and C-D and you want to know is the second in or outside of the other. But I always revert to drawing some ranges and check my logic to see will this give what I want.

How to tell in SQL Partition if plan was renewed without lapse?

2 Answers2