How to merge date range with a condition by a query-CodePudding

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

CodePudding user response：

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>