By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (NAME_, DATE_, PARAM_, VALUE) AS
SELECT 'A', DATE '2023-09-26', 'ABOV', NULL FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-26', 'NEXT', 11 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-27', 'NEXT', 10 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-28', 'NEXT', 12 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-25', 'NEXT', 2 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-28', 'ABOV', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-28', 'NEXT', 4 FROM DUAL;
7 rows affected
CREATE TABLE table_name2 (NAME_, DATE_, PARAM_, VALUE) AS
SELECT 'A', DATE '2023-09-26', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-26', 'NEXT', 11 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-27', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-27', 'NEXT', 10 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-28', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-28', 'NEXT', 12 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-29', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'A', DATE '2023-09-29', 'NEXT', 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-25', 'ABOV', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-25', 'NEXT', 2 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-26', 'ABOV', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-26', 'NEXT', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-27', 'ABOV', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-27', 'NEXT', NULL FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-28', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-28', 'NEXT', 4 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-29', 'ABOV', 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2023-09-29', 'NEXT', 0 FROM DUAL;
18 rows affected
-- INPUT
SELECT * FROM table_name
| NAME_ | DATE_ | PARAM_ | VALUE |
|---|---|---|---|
| A | 2023-09-26 00:00:00 | ABOV | null |
| A | 2023-09-26 00:00:00 | NEXT | 11 |
| A | 2023-09-27 00:00:00 | NEXT | 10 |
| A | 2023-09-28 00:00:00 | NEXT | 12 |
| B | 2023-09-25 00:00:00 | NEXT | 2 |
| B | 2023-09-28 00:00:00 | ABOV | null |
| B | 2023-09-28 00:00:00 | NEXT | 4 |
-- OUTPUT
SELECT * FROM table_name2
| NAME_ | DATE_ | PARAM_ | VALUE |
|---|---|---|---|
| A | 2023-09-26 00:00:00 | ABOV | 0 |
| A | 2023-09-26 00:00:00 | NEXT | 11 |
| A | 2023-09-27 00:00:00 | ABOV | 0 |
| A | 2023-09-27 00:00:00 | NEXT | 10 |
| A | 2023-09-28 00:00:00 | ABOV | 0 |
| A | 2023-09-28 00:00:00 | NEXT | 12 |
| A | 2023-09-29 00:00:00 | ABOV | 0 |
| A | 2023-09-29 00:00:00 | NEXT | 0 |
| B | 2023-09-25 00:00:00 | ABOV | null |
| B | 2023-09-25 00:00:00 | NEXT | 2 |
| B | 2023-09-26 00:00:00 | ABOV | null |
| B | 2023-09-26 00:00:00 | NEXT | null |
| B | 2023-09-27 00:00:00 | ABOV | null |
| B | 2023-09-27 00:00:00 | NEXT | null |
| B | 2023-09-28 00:00:00 | ABOV | 0 |
| B | 2023-09-28 00:00:00 | NEXT | 4 |
| B | 2023-09-29 00:00:00 | ABOV | 0 |
| B | 2023-09-29 00:00:00 | NEXT | 0 |
SELECT
t.NAME_,
t.DATE_,
t.PARAM_,
LAST_VALUE(VALUE_) IGNORE NULLS OVER (
PARTITION BY t.NAME_, t.PARAM_ ORDER BY d.DATE_) AS VALUE_
FROM
(SELECT DISTINCT DATE_ FROM table_name) d
LEFT OUTER JOIN
(SELECT NAME_, DATE_, PARAM_, COALESCE(VALUE, 0) as VALUE_
FROM table_name
WHERE PARAM_ in ('ABOV', 'NEXT')) t
PARTITION BY
(NAME_, PARAM_) ON (t.DATE_ = d.DATE_)
ORDER BY
NAME_, PARAM_, DATE_
| NAME_ | DATE_ | PARAM_ | VALUE_ |
|---|---|---|---|
| A | 2023-09-26 00:00:00 | ABOV | 0 |
| A | null | ABOV | null |
| A | null | ABOV | 0 |
| A | null | ABOV | 0 |
| A | 2023-09-26 00:00:00 | NEXT | 11 |
| A | 2023-09-27 00:00:00 | NEXT | 10 |
| A | 2023-09-28 00:00:00 | NEXT | 12 |
| A | null | NEXT | null |
| B | 2023-09-28 00:00:00 | ABOV | 0 |
| B | null | ABOV | null |
| B | null | ABOV | null |
| B | null | ABOV | null |
| B | 2023-09-25 00:00:00 | NEXT | 2 |
| B | 2023-09-28 00:00:00 | NEXT | 4 |
| B | null | NEXT | 2 |
| B | null | NEXT | 2 |