Search code examples
sqloracle-databaselaglead

Overlaping dates

*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
Solution

  • 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