We help IT Professionals succeed at work.

Update Query find most current date for criteria

I have the following Update query that needs to include price from another year if no price exist for matching year.

update #Results
set Price = PL.[UPrice]
from #Results R inner join PriceList PL on R.Item = PL.Item and Left(R.Itemdate, 4) = convert(varchar(4), PL.FY)

So for the part
Left(R.Itemdate, 4) = convert(varchar(4),  PL.FY)

How can I change it if there is no match for Itemdate, it looks for the most current date in PriceList?

Part of a stored procedure in SQL Server 2008.
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
update #Results
set Price = ca.[UPrice]
from #Results R
cross apply (
    select top (1) UPrice
    from PriceList PL
    where
        R.Item = PL.Item and
        Left(R.Itemdate, 4) = convert(varchar(4), PL.FY)
    order by PL.FY desc
) as ca
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What's the data type for Itemdate and FY?

Author

Commented:
ScottPletcher - I think this will work but I'll will need to tweak this part of the query:

Left(R.Itemdate, 4) = convert(varchar(4), PL.FY)

If there is no match here is why I would want the next available Year. Each year a price list comes out and new prices for the items are entered. If an Item doesn't have a price for selected year, need to pull the top price available for that item.

Author

Commented:
I got this to work. Just had to make a slight change on the Where clause. But did what I needed. Thank You.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
D'OH, sorry, quite right, I should have done the date comparison more like this:
    convert(varchar(4), PL.FY) <= Left(R.Itemdate, 4)