Oracle sql set end date based on previous start date-CodePudding

I have one table where I need to add new column endDate for future implementation but since we have currently only start date for all records I need to set endDate which should be equal to start date from previous record that are connected by userId and if it is only one record for that user than end date will have some value in future. For example:

Table structure:

ID | USER_ID | START_DATE | END_DATE
-------------------------------------
1  | 1         | 01.01.2015 |
2  | 1         | 01.01.2016 |
3  | 1         | 01.07.2018 |
4  | 1         | 01.08.2021 |
5  | 2         | 01.01.2015 |
6  | 3         | 01.01.2016 |
7  | 3         | 01.07.2018 |
8  | 4         | 01.08.2021 |

Expected result should be like this

ID | USER_ID | START_DATE | END_DATE
-------------------------------------
1  | 1         | 01.01.2015  | 01.01.2016
2  | 1         | 01.01.2016  | 01.07.2018
3  | 1         | 01.07.2018  | 01.08.2021
4  | 1         | 01.08.2021  | 01.01.2050
5  | 2         | 01.01.2015  | 01.01.2050
6  | 3         | 01.01.2016  | 01.07.2018
7  | 3         | 01.07.2018  | 01.01.2050
8  | 4         | 01.08.2021  | 01.01.2050

Can someone help me with how query in oracle databse should look to update it like this? I've tried something with for loop but not sure how to continue from this step

DECLARE     
  CURSOR c_contract         
      IS         
      SELECT             
          USER_ID         
      FROM             
          CONTRACT
      ORDER_BY START_DATE
 BEGIN     
  FOR r_contract IN c_contract        
      LOOP              
          dbms_output.put_line( r_contract.USER_ID );         
      END LOOP;
 END;

CodePudding user response：

Use the LEAD analytic function with the default date as the third argument:

SELECT t.*,
       LEAD( start_date, 1, DATE '2050-01-01') OVER (
         PARTITION BY user_id
         ORDER BY start_date
       ) AS end_date
FROM   table_name t

Which, for the sample data:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE TABLE table_name ( ID, USER_ID, START_DATE ) AS
SELECT 1, 1, DATE '2015-01-01' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2016-01-01' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2018-07-01' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2021-08-01' FROM DUAL UNION ALL
SELECT 5, 2, DATE '2015-01-01' FROM DUAL UNION ALL
SELECT 6, 3, DATE '2016-01-01' FROM DUAL UNION ALL
SELECT 7, 3, DATE '2018-07-01' FROM DUAL UNION ALL
SELECT 8, 4, DATE '2021-08-01' FROM DUAL;

Outputs:

ID	USER_ID	START_DATE	END_DATE
1	1	2015-01-01 00:00:00	2016-01-01 00:00:00
2	1	2016-01-01 00:00:00	2018-07-01 00:00:00
3	1	2018-07-01 00:00:00	2021-08-01 00:00:00
4	1	2021-08-01 00:00:00	2050-01-01 00:00:00
5	2	2015-01-01 00:00:00	2050-01-01 00:00:00
6	3	2016-01-01 00:00:00	2018-07-01 00:00:00
7	3	2018-07-01 00:00:00	2050-01-01 00:00:00
8	4	2021-08-01 00:00:00	2050-01-01 00:00:00

If you want to add a column then:

ALTER TABLE table_name ADD (end_date DATE);

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         LEAD( start_date, 1, DATE '2050-01-01') OVER (
           PARTITION BY user_id
           ORDER BY start_date
         ) AS end_date
  FROM   table_name
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE SET end_date = src.end_date;

fiddle