SQL: Fill NULL values in time series with closest preceding existing value

Question

I created the following statement:

SELECT
    convert(varchar, cal.[Datum], 104) as Datum
    , snb.[Kurs] as FX_CHF_EUR_SNB
FROM [DM_MAH].[dbo].[SNB_Kurs] snb
RIGHT JOIN [dbo].[Dim_Kalender] cal
  ON snb.Datum = cal.Datum
WHERE cal.Datum >= '2019-05-14' and cal.Datum <= GETDATE()
Order by cal.Datum desc

Which gives me the following result:

Datum       FX_CHF_EUR_SNB
12.05.2020  1.051500
11.05.2020  1.052300
10.05.2020  NULL
09.05.2020  NULL
08.05.2020  1.052800
07.05.2020  1.053200
06.05.2020  1.052800
05.05.2020  1.052500
04.05.2020  1.054700
03.05.2020  NULL
02.05.2020  NULL
01.05.2020  NULL
30.04.2020  1.056900
29.04.2020  1.056000

As you can see there are NULL values for different dates in the time series. This is expected because where I get the data from they don't have values for every day of the year. For my purpose I need the completed time series with every day of the year though, which is why I joined it with a calendar table. So far so good.

What I am not able to do, is, fill the NULL values with the closest preceding existing value of the time series. So here an example of what I want to achieve with the given data:

Datum       FX_CHF_EUR_SNB
12.05.2020  1.051500
11.05.2020  1.052300
10.05.2020  **1.052800**
09.05.2020  **1.052800**
08.05.2020  1.052800

so the values for 09.05.2020 and 10.05.2020 in this case should be copied from the 08.05.2020

Would be glad if anyone knew how to solve this.

Possible duplicate of [How to get Previous Value for Null Values](https://stackoverflow.com/questions/16669620/how-to-get-previous-value-for-null-values) — GSerg, May 13 '20 at 06:41

score 2 · Accepted Answer · answered May 13 '20 at 06:44

One approach uses a correlated subquery to choose the correct non NULL value to report from the SNB_Kurs table:

SELECT
    CONVERT(varchar, cal.[Datum], 104) AS Datum,
    (SELECT TOP 1 snb.[Kurs] FROM [DM_MAH].[dbo].[SNB_Kurs] snb
     WHERE snb.Datum <= cal.Datum AND snb.Datum IS NOT NULL
     ORDER BY snb.Datum DESC) AS FX_CHF_EUR_SNB
FROM [dbo].[Dim_Kalender] cal
WHERE cal.Datum >= '2019-05-14' AND cal.Datum <= GETDATE()
ORDER BY cal.Datum DESC;

With this i was able to solve my problem! Thank you. – nox May 13 '20 at 12:58 — nox, May 13 '20 at 12:58

SQL: Fill NULL values in time series with closest preceding existing value

1 Answers1