How about another one?

Given two table for storing product and pricing information:

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:

Product table

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-15

Write a stored procedure that returns a price list as of a supplied date. For instance

EXECUTE p_generatepricelist '19991008'

would return:

1  Diet Coke - 6pack  1.05
2  Diet Coke - 2liter 1.49

And

EXECUTE p_generatepricelist '19991013'

would return:

1  Diet Coke - 6pack  0.75
2  Diet Coke - 2liter 0.99

It can be done in one query.

-Mike