August 31, 2009 at 3:10 pm
Hi folks, I get the feeling I am overlooking the obvious and ending up tying myself in knot trying something complex which I think should be straight forward.
I have two tables in my database MS SQL 2005.
Table Orders contains fields Customer, OrderDate, Part, Rate
Table Prices contains fields Part, RateStartDate, Rate
Obviously when our pricing changes we insert a new rate for each part into the Prices table.
When orders are entered into Orders table we have the customer, OrderDate and part, we need to find the rate.
What I have done in the past is use Excel VBA to find the part and do a MAX(RateStartDate) and ensure that the OrderDate >= RateStartDate. Giving me the newest possible date for that part. I then use that MAX(RateStartDate) with the Part number in another query to get the exact rate. 1 record matches.
What I want to do is build this into a UPDATE SET statement within my SQL server but I am getting embedded select returning more that 1 record errors. I suspect because of the structure of my statement that it's finding every record in the Orders table for that part where the RateStartDate is applicable.
My stored proc contains the following statement.
UPDATE dbo.Orders
SET Rate = (Select dbo.Prices.Rate From dbo.Prices, dbo.Orders Where dbo.Prices.RateStartDate = (select max(dbo.Prices.RateStartDate) from dbo.Prices, dbo.Orders where dbo.Prices.Part = dbo.Orders.Part and dbo.Orders.OrderDate>= dbo.Prices.RateStartDate))
Any ideas how to simplify? I was originally going to break this down into many parts but would then have to loop through entire Orders table when data is downloaded where I felt an update statement could provide the loop.
August 31, 2009 at 3:41 pm
This may work, but it isn't tested. there may also be a better way to write it as well.
UPDATE dbo.Orders SET
Rate = p.Rate
FROM
dbo.Prices p
inner join dbo.Orders o
on (p.Part = o.Part
and p.RateStartDate = (select
max(p1.RateStartDate)
from
dbo.Prices p1
where
p1.Part = o.Part
and p1.RateStartDate <= o.OrderDate));
September 1, 2009 at 12:00 am
Thanks Lynn,
I have ran this and it works a treat, just what I need to begin taking Excel out of the equation. Cheers.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply