Retrieve the last valid value from a previous date-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

You can do some self-joins and embedded sub queries to make it work. I don't have a SQL server handy to access, but the query would look something like the below.

SELECT aDate, realRate
  FROM Rates
 WHERE IsBusinessDay = 1
UNION ALL
SELECT sub.aDate, r3.realRate
  FROM Rates r3
        INNER JOIN (
            SELECT r1.aDate, 
                   (SELECT MAX(aDate) 
                      FROM Rates r2 
                     WHERE r2.aDate < r1.aDate 
                       AND r2.isBusinessDay = 1) as LastDate
              FROM Rates r1
             WHERE r1.IsBusinessDay = 0
             ) sub
            ON r3.aDate = sub.LastDate

Basically:

take all the business days (first query) add onto it all the non-business days. For those:
For each non-business day, an embedded subquery to get the MAX() that is a business day that is less than the non-business day.
Then join to the actual table to get the rate for that business day.

Note that embedded queries like that can be expensive if the number of rows in the table is really large, but since you appear to have 1 row per day, you shouldn't have millions of rows. The reason is that since it's using the r1 table as a filter criteria, the server has to re-execute the subquery for each row in r1.