Value from 2. highest date in a column - dynamically

Question

I have a table like:

CustomerID|ValueName|Value|Date
1         |XYZ      |1000 |2020-04-30
1         |XYZ      |2000 |2020-03-31
1         |XYZ      |5000 |2020-02-29
1         |XYZ      |4000 |2020-01-31

Now for a view, I would like to add a new column, which dynamically shows the value of the last date, e.g:

CustomerID|ValueName|Value|Date      |ValueDateBefore
1         |XYZ      |1000 |2020-04-30|2000
1         |XYZ      |2000 |2020-03-31|5000
1         |XYZ      |5000 |2020-02-29|4000
1         |XYZ      |4000 |2020-01-31|0

So my question now is, is there a way to make this someway dynamic? What is the best way to do this?

Answer 1

You can achive that by using the LAG() function

SELECT CustomerID
    , ValueName, Value
    , Date
    , LAG(VALUE) OVER (PARTITION BY CustomerID,ValueName ORDER BY DATE) AD ValueDateBefore
FROM YourTable
ORDER BY CustomerID,ValueName,Date desc