Search code examples
sqloracle-databasewindow-functionsoracle12c

Update current row of Date Column based on the Previous row of different Date column

Please help to achieve this in Oracle SQL.

I have table structure like below ,

enter image description here

create table temp_t 
(
    rule_id number, 
    start_datetime timestamp, 
    end_datetime timestamp,
    last_update timestamp 
);

insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:22:37','YYYY-MM-DD HH24:MI:SS'),null,null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:18:36','YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2023-10-02 15:22:36','YYYY-MM-DD HH24:MI:SS'),null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:15:18','YYYY-MM-DD HH24:MI:SS'),null,null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:11:02','YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2023-10-02 15:12:10','YYYY-MM-DD HH24:MI:SS'),null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 14:03:02','YYYY-MM-DD HH24:MI:SS'),null,null);

Table data :

Current table data

Expected Result to be like below , NULL values on the END_DATETIME to be updated based on the START_DATETIME column from previous row with minus 1 second. Latest row of END_DATETIME NULL should not not be updated.

RULE_ID | START_DATETIME                 | END_DATETIME                  |
-----------------------------------------------------------------------
2345    |02-OCT-23 03.22.37.000000000 PM |  NULL
2345    |02-OCT-23 03.18.36.000000000 PM |  02-OCT-23 03.22.36.000000000 PM
2345    |02-OCT-23 03.15.18.000000000 PM |  02-OCT-23 03.18.35.000000000 PM
2345    |02-OCT-23 03.11.02.000000000 PM |  02-OCT-23 03.12.10.000000000 PM
2345    |02-OCT-23 02.03.02.000000000 PM |  02-OCT-23 03.11.01.000000000 PM
Solution

  • Redefine end time as the start time of the next row in order to get an gapless sequence:

    SELECT rule_id,
       start_datetime,
       LEAD(start_datetime) OVER (PARTITION BY rule_id ORDER BY start_datetime) AS end_datetime
  FROM temp_t

    Or, if you want gaps should the existing end_datetime values be correct even if the next start time is in its future, you can keep the values you have and only replace them when they are NULL:

    SELECT rule_id,
       start_datetime,
       NVL(end_datetime,LEAD(start_datetime) OVER (PARTITION BY rule_id ORDER BY start_datetime)) AS end_datetime
  FROM temp_t

    If you are wanting to update the table rather than pull this with a SELECT, then you don't even need windowing functions:

    UPDATE temp_t t1
   SET end_datetime = (SELECT MIN(t2.start_datetime)
                         FROM temp_t t2
                        WHERE t2.rule_id = t1.rule_id
                          AND t2.start_datetime > t1.start_datetime)
 WHERE end_datetime IS NULL;