Retrieve the last valid value from a previous date

I have the following table “Rates”:

aDate	realRate	isBusinessDay
01-04-22	1.10	1
02-04-22	1.20	1
03-04-22	1.30	1
04-04-22	1.40	0
05-04-22	1.50	0
06-04-22	1.60	1
07-04-22	1.70	1
08-04-22	1.80	1
09-04-22	1.90	1
10-04-22	2.00	0
11-04-22	2.10	0
12-04-22	2.20	0
13-04-22	2.30	0
14-04-22	2.40	1
15-04-22	2.50	1
16-04-22	2.60	1
17-04-22	2.70	1
18-04-22	2.80	0
19-04-22	2.90	0
20-04-22	3.00	0
21-04-22	3.10	1

I need to build a query that will return, for the non business days, the rate at the previous known business day. Column “useThisRate” is the required result of the query:

aDate	realRate	isBusinessDay	useThisRate
01-04-22	1.10	1	1.10
02-04-22	1.20	1	1.20
03-04-22	1.30	1	1.30
04-04-22	1.40	0	1.30
05-04-22	1.50	0	1.30
06-04-22	1.60	1	1.60
07-04-22	1.70	1	1.70
08-04-22	1.80	1	1.80
09-04-22	1.90	1	1.90
10-04-22	2.00	0	1.90
11-04-22	2.10	0	1.90
12-04-22	2.20	0	1.90
13-04-22	2.30	0	1.90
14-04-22	2.40	1	2.40
15-04-22	2.50	1	2.50
16-04-22	2.60	1	2.60
17-04-22	2.70	1	2.70
18-04-22	2.80	0	2.70
19-04-22	2.90	0	2.70
20-04-22	3.00	0	2.70
21-04-22	3.10	1	3.10

Please note that the number of sequential non business days is unknown.

Any help will be appreciated.

Answer

Probably the most intuitive way would be a correlated query.

You either want the realRate or if non-business day the most recent realRate where it’s a business day and the date is earlier than the current row.

select *,
  case when isBusinessDay = 1 then realrate
    else (
      select top(1) realrate
        from rates r2
        where r2.isBusinessDay = 1 and r2.aDate < r.adate
        order by adate desc
    ) 
  end UseThisRate
from rates r;