change price in table w/out changing price in past records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out how, in the Northwind example, the prices go up in the product table, but the detail for past orders maintains the previous price.

When I change the price in my table, all my orders that I previously entered, the price changes as well.

Any help would be greatly appreciated!
 
I'm not familiar with the northwind database, but here's the idea:

You can make another table called PriceHistory, with the fields ProductKey,
Price, StartDate. The primairykey is the three columns together. The
pricefield is now no longer in the products table but in this table. Here
you can enter new prices with their startingdate so you keep a history of
all the prices. To join a price to a product you use:

SELECT
Products.Name,
Max(PriceHistory.Price) AS Price
FROM
Products INNER JOIN PriceHistory ON Products.Key =
PriceHistory.ProductKey
WHERE PriceHistory.StartDate<=Date()
GROUP BY Products.Name;

The same goed for orders, but then you join the orders table in there as
well and the whereclause becomes:
WHERE PriceHistory.Startdate<=Orders.OrderDate

Hope this helps,

Regards Gert-Jan

kinberly said:
I am trying to figure out how, in the Northwind example, the prices go up
Click to expand...
in the product table, but the detail for past orders maintains the previous
price.
When I change the price in my table, all my orders that I previously
Click to expand...
entered, the price changes as well.
 
Oops, BIG Mistake. (it's been a while) Forget the previous message!

This is it:
Make a new tabel called PriceHistory with the following fields:
ProductKey Numeric :Foreignkey of the related product
StartDate Date/Time :Date that the price was raised(or
lowered) to the price in this record
Price Currency :The price

Make the first two columns the primairy key. (don't forget or this will be a
slow query.) If you also make a relationship between the ProductKey in this
table and the Key column in the products table you get to use the nifty
subdatasheet feature so it's easier to use this all.

In this table, you keep a log of all the prices that ever were for this
product with their startingdate. For the current price of an item use the
following query:

SELECT
Products.Name,
PriceHistory.Price AS Price
FROM
Products INNER JOIN PriceHistory ON
Products.Key = PriceHistory.ProductKey
WHERE
PriceHistory.StartDate=(SELECT MAX(StartDate) FROM PriceHistory WHERE
PriceHistory.ProductKey = Products.Key AND StartDate<=Date());


When you want to find the price for an order, inner join products,
pricehistory, orders and orderlines (i presume it's like this) and the
instead of the "date()" function at the end type "Orders.Date" So you take
the price for the day the order was placed instead of today. Hope this
helps.

GJ
 
Back
Top