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.
MadIceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the data type for Itemdate and FY?
0
MadIceAuthor 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.
0
MadIceAuthor Commented:
I got this to work. Just had to make a slight change on the Where clause. But did what I needed. Thank You.
0
Scott PletcherSenior DBACommented:
D'OH, sorry, quite right, I should have done the date comparison more like this:
    convert(varchar(4), PL.FY) <= Left(R.Itemdate, 4)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.