February 8, 2012 at 9:43 am
Hello -
I have an orders table and a MSRP (manufacturer suggested retail price) table. I would like to grab the date specific MSRP and add it to the orders table.
Orders table has the following fields
Order_Date
UPC
MSRP - I want to add this field
MSRP table has the following fields
UPC
Effective_Date
Price
How would I grab the correct price from the MSRP table and lay it into the orders table?
If I have an order date of 1-1-11 and the MSRP for that UPC looks like this:
55555555555 | 4-5-09 | $1.99
55555555555 | 3-9-11 | $1.65
55555555555 | 9-4-10 | $2.25
Then I would need to choose the $2.25 because it is the most recent price update without being after the order date. Hope this all makes sense!
February 8, 2012 at 10:05 am
notice how i provided all the code you might need to test the solution? if you can do the same, by providing either CTE or CREATE TABLE...INSERT INTO statements, you'll get much better results, faster, and in a way that everyone can follow along.
one way to do it is to use row_number and simply grab the right row...
play with this, see if you understand why it returns the results you want, and ask any questions you might have.
WITH Orders(UPC,Order_Date,pricePaid)
AS(
SELECT convert(bigint,55555555555),convert(datetime,'01-1-11'),convert(money,187 )
)
,MSRP (UPC,Effective_Date ,Price )
AS
(
SELECT convert(bigint,55555555555),convert(datetime,'4-5-09'),convert(money,1.99) UNION ALL
SELECT 55555555555,'3-9-11',1.65 UNION ALL
SELECT 55555555555,'9-4-10',2.25
)
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY Orders.UPC ORDER BY MSRP.Effective_Date DESC) As RW,
Orders.*,
MSRP.Effective_Date,
MSRP.Price AS MSRP_PRice
FROM Orders
LEFT OUTER JOIN MSRP
ON Orders.UPC = MSRP.UPC
AND Orders.Order_Date >= MSRP.Effective_Date ) MyAlias
WHERE RW = 1
Lowell
February 8, 2012 at 10:41 am
Thanks for taking the time to help Lowell - much appreciated!
I am not very familiar with Row_Number or the Partition By functions in T-SQL, I am going to have to do some reading on them. I just tested out your solution on some data. It kind of worked, but I selected the top 1000 rows and it only returns 17 results. At first glance it looks like the Partition By function essentially groups by UPC. This is a cool view, but not exactly what I am looking for.
I don't want to just view the results, I want to add an actual column to the orders table and insert the date specific MSRP so it is always there. We are in a data warehouse environment, and would like it there to run reports on the table. It should be a permanent column moving forward. I will also have to build a solution to add the date specific MSRP in SSIS during loads.
February 8, 2012 at 11:30 am
you probably need to partition by customer number + upc or invoice_number + upc instead...it completely depends on your specific data. based on what i was able to derive, my limited example works, but it's a prototype for enhancement.
if you can provide sample tables and data that truely emulates the situation, we can help further.
Lowell
February 8, 2012 at 1:08 pm
I find that row number works better when you have a single table that you are trying to pull the most recent record from. When you have multiple tables, I think that CROSS APPLY performs much better.
SELECT *
FROM Orders AS o
CROSS APPLY (
SELECT TOP(1) m.Effective_Date, m.Price
FROM MSRP AS m
WHERE o.UPC = m.UPC
AND o.Order_Date >= m.Effective_Date
ORDER BY m.Effective_Date DESC
) AS m
Drew
PS: Since the sample data was so small, I wasn't able to generate meaningful statistics for IO and TIME.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 8, 2012 at 1:12 pm
Thanks Lowell. I wish I would provide actual data but due to regulatory compliance laws I cannot. I am actually not dealing with orders, upcs, or msrps...it just makes a good comparison for people to understand.
With that said, partitioning by UPC and RowID has fixed the problem!
February 8, 2012 at 1:28 pm
Lowell (2/8/2012)
if you can provide sample tables and data that truly emulates the situation, we can help further.
the key there was emulates what you are trying to do, not necessarily exactly what you are trying to do.
everyone needs to protect the real data of course, in the future just give us something that properly represents the data in question.
glad you got it working the way you wanted with the rowid you had available!
Lowell
February 9, 2012 at 12:42 pm
Thanks Drew, I really like the Cross Apply method. Much easier to read. I wonder how performance differs between the two methods...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply