• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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.
0
MadIce
Asked:
MadIce
  • 2
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now