How to merge date range with a condition by a query

I would like to merge date range with the condition as example below

Do you have an example for this case?

I have tried to search for days.

The actual data by select * from query

Group / Start_date / End_date / Value
1 / 31-Dec-2020 / 3-Jan-2021 / 0.175
1 / 4-Jan-2021 / 29-Jun-2021 / 0.175
1 / 30-Jun-2021 / 28-Feb-2022 / 0.175
1 / 1-Mar-2022 / 1-Jan-4000 / [NULL]

My expected result set as example below,

Group / Start_date / End_date / Value
1 / 31-Dec-2020 / 3-Jan-2021 / 0.175
1 / 4-Jan-2021 / 29-Jun-2021 / 0.175
1 / 30-Jun-2021 / 1-Jan-4000 / 0.175

I would like to merge 2 data range records in a record if the next date range has null value

PS. Sorry for my broken English

>Solution :

To me, it looks as if the lead analytic function (and then case expression) might help.

Sample data:

SQL> WITH
  2     test (grp,
  3           start_date,
  4           end_date,
  5           VALUE)
  6     AS
  7        (SELECT 1, DATE '2020-12-31', DATE '2021-01-03', 0.175 FROM DUAL
  8         UNION ALL
  9         SELECT 1, DATE '2021-01-04', DATE '2021-06-29', 0.175 FROM DUAL
 10         UNION ALL
 11         SELECT 1, DATE '2021-06-30', DATE '2022-02-28', 0.175 FROM DUAL
 12         UNION ALL
 13         SELECT 1, DATE '2022-03-01', DATE '4000-01-01', NULL FROM DUAL),

Query begins here:

 14     temp
 15     AS
 16        (SELECT grp,
 17                start_date,
 18                VALUE,
 19                LEAD (VALUE) OVER (PARTITION BY grp ORDER BY start_date)
 20                   next_value,
 21                --
 22                end_date,
 23                LEAD (end_date) OVER (PARTITION BY grp ORDER BY start_date)
 24                   next_end_date
 25           FROM test)
 26    SELECT grp,
 27           start_date,
 28           CASE WHEN next_value IS NULL THEN next_end_date ELSE end_date END
 29              end_date,
 30           VALUE
 31      FROM temp
 32     WHERE VALUE IS NOT NULL
 33  ORDER BY grp, start_date;

       GRP START_DATE  END_DATE     VALUE
---------- ----------- ----------- ------
         1 31-dec-2020 03-jan-2021  0.175
         1 04-jan-2021 29-jun-2021  0.175
         1 30-jun-2021 01-jan-4000  0.175

SQL>