Last observation carried forward query (SELECT from-between incomplete time series)

Question

I have a table that gets a new row only when some recalculations are completed (TOTAL column)

DATE_REFRESH    TOTAL
2020-08-28     40000.00
2020-09-04     60000.00
2020-09-22    100000.00
2020-10-13    125000.00

I need to SELECT a date 2020-09-15 and return 60 000 which is the last amount before the next update/refresh on 2020-09-22

Desired result: 60 000

Ty!

Answer 1

You can use order by and top (1):

select top (1) *
from mytable 
where date_refresh <= '20200915'
order by date_refresh desc

Answer 2

I think I understood your problem and my approach was to solve it in SQL was with DATEDIFF(). So the SQL Script would be following:

SELECT TOTAL FROM (*your table*)
WHERE DATE_REFRESH = (
 SELECT TOP 1 DATE_REFRESH
 FROM (*your table*)
 WHERE DATE_REFRESH < (*your date*)
 ORDER BY DATEDIFF ( (*your date*), DATE_REFRESH )
)

I'm selecting TOTAL from the row where the nearest (TOP 1 FROM - ORDER BY DATEDIFF()) smaller (DATE_REFRESH < (*your date*)) date to your date is.

I hope it helped. :)