Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE variables ( variables_id, variables_name, variables_value, variables_updated_at ) as
SELECT 1, 'maintenance_price', 30.00, TIMESTAMP '2020-08-01 05:00:00.000' from dual;
1 rows affected

CREATE TABLE variables_history ( variables_id, variables_name, variables_value, variables_hist_updated_at ) as
SELECT 1, 'maintenance_price', 15.90, TIMESTAMP '2019-10-01 11:30:00.000' from dual union all
SELECT 1, 'maintenance_price', 10.50, TIMESTAMP '2020-01-01 01:00:00.000' from dual union all
SELECT 1, 'maintenance_price', 20.30, TIMESTAMP '2020-05-01 12:30:00.000' from dual;
3 rows affected

CREATE TABLE maintenances ( maintenance_id, maintenance_status, maintenance_date ) as
SELECT 1, 'COMPLETE', TIMESTAMP '2019-02-01 00:30:00.000' from dual union all
SELECT 2, 'COMPLETE', TIMESTAMP '2019-05-01 01:30:00.000' from dual union all
SELECT 3, 'COMPLETE', TIMESTAMP '2019-11-01 02:30:00.000' from dual union all
SELECT 4, 'COMPLETE', TIMESTAMP '2020-07-10 05:30:00.000' from dual union all
SELECT 5, 'FAILED',   TIMESTAMP '2020-08-02 11:30:00.000' from dual union all
SELECT 6, 'COMPLETE', TIMESTAMP '2020-08-20 11:30:00.000' from dual;
6 rows affected

SELECT *
FROM   (
  SELECT maintenance_id,
         COALESCE(
           variables_value,
           LAG( variables_value, 1 ) IGNORE NULLS OVER ( ORDER BY maintenance_date ),
           LEAD( variables_value, 1 ) IGNORE NULLS OVER ( ORDER BY maintenance_date )
         ) AS variables_value,
         maintenance_date
  FROM   (
    SELECT maintenance_id,
           NULL AS variables_value,
           maintenance_date
    FROM   maintenances
    WHERE  maintenance_status = 'COMPLETE'
    UNION ALL
    SELECT NULL,
           variables_value,
           variables_updated_at
    FROM   variables
    UNION ALL
    SELECT NULL,
           variables_value,
           variables_hist_updated_at
    FROM   variables_history
  )
)
WHERE maintenance_id IS NOT NULL;


MAINTENANCE_ID
VARIABLES_VALUE
MAINTENANCE_DATE


1
15.9
01-FEB-19 00.30.00.000000000

2
15.9
01-MAY-19 01.30.00.000000000

3
15.9
01-NOV-19 02.30.00.000000000

4
20.3
10-JUL-20 05.30.00.000000000

6
30
20-AUG-20 11.30.00.000000000


SELECT *
FROM   (
  SELECT maintenance_id,
         COALESCE(
           variables_value,
           LEAD( variables_value, 1 ) IGNORE NULLS OVER ( ORDER BY maintenance_date ),
           LAG( variables_value, 1 ) IGNORE NULLS OVER ( ORDER BY maintenance_date )
         ) AS variables_value,
         maintenance_date
  FROM   (
    SELECT maintenance_id,
           NULL AS variables_value,
           maintenance_date
    FROM   maintenances
    WHERE  maintenance_status = 'COMPLETE'
    UNION ALL
    SELECT NULL,
           variables_value,
           variables_updated_at
    FROM   variables
    UNION ALL
    SELECT NULL,
           variables_value,
           variables_hist_updated_at
    FROM   variables_history
  )
)
WHERE maintenance_id IS NOT NULL;


MAINTENANCE_ID
VARIABLES_VALUE
MAINTENANCE_DATE


1
15.9
01-FEB-19 00.30.00.000000000

2
15.9
01-MAY-19 01.30.00.000000000

3
10.5
01-NOV-19 02.30.00.000000000

4
30
10-JUL-20 05.30.00.000000000

6
30
20-AUG-20 11.30.00.000000000
MAINTENANCE_ID	VARIABLES_VALUE	MAINTENANCE_DATE
1	15.9	01-FEB-19 00.30.00.000000000
2	15.9	01-MAY-19 01.30.00.000000000
3	15.9	01-NOV-19 02.30.00.000000000
4	20.3	10-JUL-20 05.30.00.000000000
6	30	20-AUG-20 11.30.00.000000000