August 29, 2014 at 4:14 am
Something I thought should be easy and probably is, but I've been unable to find a working solution.
I need to extract a price from a package solution table that was current on a certain date.
Columns are:
Product_Reference int,
Change_Date int, -- Note that this is in yyyymmdd format
Price decimal
So for one items you would get
Product_ReferenceChange_DatePrice
100014200401281.59
100014200605131.75
100014200802121.99
100014200906252.35
100014201002242.50
100014201107151.10
100014201205151.15
The challenge is to return the price of the item on 20070906. In this example 1.75.
I've tried joining it to itself
SELECT DISTINCT p1.[Product_Reference]
,p1.[Change_Date]
,p1.[Price]
FROM PHistory p1
INNER JOIN PHistory p2
ON p1.Product_Reference = p2.Product_Reference
WHERE p1.Product_Reference = 100014
AND p1.Change_Date >= 20070906
and p2.Change_Date < 20070906
But it returns the price above and below that date.
Anyone care to point out where I'm going wrong please?
August 29, 2014 at 4:34 am
SELECT
Price
FROM
PHistory
WHERE
Product_Reference = 100014
AND
Change_Date = (
SELECT
MAX(Change_Date)
FROM
PHistory
WHERE
Product_Reference = 100014
AND
Change_Date < 20070906
)
Of course, you may get into trouble if the price changes more than once on the same date.
John
August 29, 2014 at 5:22 am
Many thanks.
Technically its possible, but in 8 years it never has. However, there is a counter that increments in that situation so I should be able to use the same logic.
September 1, 2014 at 7:25 pm
I think you might want to take a look at the following article, which compares various alternative solutions to this problem along with their performance characteristics:
Creating a Date Range from Multiple Rows Based on a Single Date[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply