Get nearest non nulls value fill if null

Question

I get a table just 3 records like:

Date          Amt
02/04/2023    100
03/04/2023    102
06/04/2023    200

Is there any way I can get non nulls value fill null like this: (from table we do not have 04/04 and 05/04 then get nearest value from 03/04)

Date           Amt
02/04/2023     100
03/04/2023     102
04/04/2023     102
05/04/2023     102
06/04/2023     200

I am using Oracle 19c.

Does this answer your question? [Duplicating records to fill gap between dates](https://stackoverflow.com/questions/10254769/duplicating-records-to-fill-gap-between-dates) — astentx, May 05 '23 at 11:05

Sayan Malakshinov · Accepted Answer · 2023-05-06T00:39:05.607

1

One of the easiest solutions:

with t(date_dt,Amt) as (
 select date'2023-04-02', 100 from dual union all
 select date'2023-04-03', 102 from dual union all
 select date'2023-04-06', 200 from dual
)
select 
   missing_dt as dt
  ,amt 
from (
     select date_dt,amt, lead(date_dt)over(order by date_dt) next_dt
     from t
     )
     cross apply (
        select date_dt+level-1 as missing_dt
        from dual
        connect by date_dt+level<=next_dt
     );

DBFiddle: https://dbfiddle.uk/-6zXTodR

edited May 06 '23 at 00:39

answered May 05 '23 at 10:34

Sayan Malakshinov

8,492
1
19
27

This is repeating the same day instead of filling the missing ones. See the fiddle and correct it, please... @Sayan Malakshinov – d r May 05 '23 at 11:40

score 1 · Answer 2 · answered May 05 '23 at 11:15

Use a recursive cte to get the missing dates.

Use last_value ignore nulls to get the latest current/previous (non-null) value.

with dates (dat) as (
  select min(dat) from t
  union all
  select dat + interval '1' day from dates
  where dat < (select max(dat) from t)
  )
select d.dat, last_value(t.amt) ignore nulls over (order by d.dat)
from dates d
left join t on d.dat = t.dat
order by d.dat

https://dbfiddle.uk/bkxuhZFX

d r · Answer 3 · 2023-05-05T11:43:23.527

One of the options could be to do it using analytic function LAST_VALUE() with dataset prepared with LEVEL ... Connect By filling the missing days:

WITH        -- Sample Data
    tbl (DT, AMNT) AS
        ( Select To_date( '02.04.2023', 'dd.mm.yyyy'), 100 From Dual Union All
            Select To_date( '03.04.2023', 'dd.mm.yyyy'), 102 From Dual Union All
            Select To_date( '06.04.2023', 'dd.mm.yyyy'), 200 From Dual
        )
--    M a i n   S Q L :
SELECT    X_DT "DT", 
          Nvl(AMNT, LAST_VALUE(AMNT IGNORE NULLS) Over(Order By X_DT Rows Between Unbounded Preceding And 1 Preceding)) "AMNT"
FROM  (   Select      DAY_ID, t.DT, t.AMNT, 
                    x.MIN_DT, x.MAX_DT, x.MNTH_ID, 
                    Case  When DAY_ID <= To_Char(LAST_DAY(To_Date('01.' || MNTH_ID, 'dd.mm.yyyy')), 'dd') 
                          Then To_Date(DAY_ID || '.' || MNTH_ID, 'dd.mm.yyyy') 
                    End "X_DT"
          From      (  Select LPAD(LEVEL, 2, '0') "DAY_ID" From Dual Connect By LEVEL <= 31    ) d
          Left Join ( Select To_Char(DT, 'mm.yyyy') "MNTH_ID", Min(DT) "MIN_DT", Max(DT) "MAX_DT" From tbl Group By To_Char(DT, 'mm.yyyy')) x ON(1 = 1) 
          Left Join tbl t ON(t.DT = Case  When DAY_ID <= To_Char(LAST_DAY(To_Date('01.' || MNTH_ID, 'dd.mm.yyyy')), 'dd') 
                                          Then To_Date(DAY_ID || '.' || MNTH_ID, 'dd.mm.yyyy') 
                                    End)
      )
WHERE X_DT Between MIN_DT And MAX_DT
ORDER BY X_DT
--
--  R e s u l t :
DT              AMNT
--------- ----------
02-APR-23        100
03-APR-23        102
04-APR-23        102
05-APR-23        102
06-APR-23        200

Get nearest non nulls value fill if null

3 Answers3