Joining two tables on the max date of table b that's less than or equal to a date in table A-CodePudding

I have a table that contains a list of all of the dates of a date range (table a), as well as another table that contains just business dates and their respective asset values (table b). I'd like to query for all values in table a, and join in the asset values from table b. In the event table A's date is not in table B, then bull in the asset value for the max date in table B that's less than or equal to the date value in table A

Table A:

DATE
1/1/2021
1/2/2021
1/3/2021
1/4/2021
1/5/2021
1/6/2021
1/7/2021
1/8/2021
1/9/2021
1/10/2021

Table B:

DATE    Assets
1/1/2021    6
1/3/2021    6
1/5/2021    3
1/7/2021    9
1/9/2021    10

Desired Results:

DATE    Assets
1/1/2021    6
1/2/2021    6
1/3/2021    5
1/4/2021    5
1/5/2021    10
1/6/2021    10
1/7/2021    9
1/8/2021    9
1/9/2021    2
1/10/2021   2

I've attempted something along the lines of the below with no luck. Any input would be greatly appreciated:

select a.[DATE],
        b.[Assets]
from A as a
left join B as B on a.DATE <= b.DATE

CodePudding user response：

One possibility is to use outer apply to get the relevant asset for a given date.

select A.[date], B1.Assets
from TableA A
outer apply (
    select top 1 Assets
    from TableB B
    where B.[date] <= A.[date]
    order by [date] desc
) B1