Join on previous date if same date is not available?-CodePudding

enter image description here

I would like to join the balance table to the orders on each UID and stock_name and dates. The date should join on the same or previous available date.

Here is my initial code but it would return null if date is not available during the order date.

Select a.date, 
    a.stock_name, 
    a.UID, 
    a.Sale,
    b.avg_price, 
    a.Sale-b.avg_price as Gain
from orders as a
left join balance as b on a.UID = b.UID and a.stock_name = b.stock_name and a.date = b.date
order by a.date desc

CodePudding user response：

I would use CROSS APPLY or OUTER APPLY (SELECT TOP 1 ...) for this. Try:

outer apply (
    select 1 *
    from balance as b
    where a.UID = b.UID
    and a.stock_name = b.stock_name
    and a.date >= b.date
    order by b.date desc
) b

This will select the closest balance date at or earlier than the order date. If the resulting b is null, there is no balance record. OUTER APPLY is like a LEFT OUTER JOIN, while CROSS APPLY is like a regular INNER JOIN. Which you use depends on how you prefer to handle the no-match condition.