CREATE TABLE product ( pd_id int INDENTITY(1,1) PRIMARY KEY NOT NULL, pd_name varchar(40) NOT NULL, ... ) CREATE TABLE product_price ( pr_id int IDENTITY(1,1) PRIMARY KEY NOT NULL, pd_id int REFERENCES product(pd_id), pr_price money NOT NULL, pr_effdate datetime NOT NULL )And some sample data might look like:
1 Diet Coke - 6pack 2 Diet Code - 2liter ...product_price
1 1 0.99 1999-07-01 2 1 1.05 1999-10-01 3 1 0.75 1999-10-11 4 1 1.05 1999-10-15 5 2 1.49 1999-07-01 6 2 0.99 1999-10-11 7 2 1.49 1999-10-15Write a stored procedure that returns a price list as of a supplied date. For instance
1 Diet Coke - 6pack 1.05 2 Diet Coke - 2liter 1.49And
1 Diet Coke - 6pack 0.75 2 Diet Coke - 2liter 0.99It can be done in one query.