Overlaping dates

Question

*Edit - No update rights.

I have the below tables: An id should be associated to one area only. There's overlap on TableB. Therefore John on 1/9/19 is associated to area East and MidEast.

Can I manipulate TableB to fix the overlapping?

So, the oldest row enddate (1/9/19) would change to 1/7/19.

There can be more than one day overlap.

Lead and or Lag be used here? Not sure where to start.

TableA

CustDate   id   Name
1/9/19      1   John

TableB

StartDate   EndDate      AREA
 1/1/2019    1/9/19      East
 1/8/2019    12/31/4000  Mideast

Example SQL

 ,CASE WHEN ENDDATE >
LEAD(STARTDATE) OVER (PARTITION BY ID ORDER BY STARTDATE) 
THEN MIN(ENDDATE) OVER (PARTITION BY ID) - interval '1' day
ELSE ENDATE
END END_DT2

Correct. one could answer the question without referring to table A but added to visually show my issue of Name being associated to multiple areas.. — Mr John, Apr 19 '19 at 13:21
Besides `StartDate, EndDate, AREA` isn't there a column like `id` referring to John's id in tableb? — forpas, Apr 19 '19 at 13:24

Gordon Linoff · Answer 1 · 2019-04-19T19:25:25.403

2

You can fix TableB by updating enddate:

update tableb b
    set enddate = (select min(startdate) - interval '1' day
                   from tableb b2
                   where b2.startdate > b.startdate
                  );

Or, express this as a select:

select b.startdate,
       lead(b.startdate, 1, date '4000-12-31') over (order by b.startdate) - interval '1' day as enddate,
       b.area
from tableb b;

edited Apr 19 '19 at 19:25

answered Apr 19 '19 at 13:19

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Apr 19 '19 at 16:03

0

Perhaps Lead and Lag

Something like below:

  ,CASE
   WHEN ENDDATE > LEAD(STARTDATE) OVER (PARTITION BY ID ORDER BY STARTDATE) 
   THEN LEAD (STARTDATE) OVER (PARTITION BY ID ORDER BY STARTDATE) -1
               ELSE ENDDATE
               END END_DT

answered Apr 19 '19 at 16:03

Mr John

231
1
3
18

If this is not an answer to your question, edit your question and put the code in it and then delete this post. – forpas Apr 19 '19 at 16:05

Overlaping dates

2 Answers2