Re: Database Design and Query

Andy (andrewngrimes_at_hotmail.com) writes:
> I am working on a system to order widgets. These widgets are rented.
> We send them and they come back to us. We charge different prices
> according to the time of the year. Here is an example of the table.
>
> CREATE TABLE [WidgetPrice] (
> [WidgetID] [int] NOT NULL ,
> [ShipDate] [smalldatetime] NOT NULL ,
> [ReturnDate] [smalldatetime] NOT NULL ,
> [Price] [int] NOT NULL ,
> )
>
> GO
>
> INSERT INTO WidgetPrice VALUES (1, '1/1/2002', '4/15/2002', 25)
> INSERT INTO WidgetPrice VALUES (1, '4/16/2002', '9/7/2002', 50)
> INSERT INTO WidgetPrice VALUES (1, '9/8/2002', '12/31/2002', 30)
>
> If someone requests this:
>
> ShipDate = "9/1/2002"
> ReturnDate = "9/20/2002"
>
> There would be 19 days.
>
> I need to get the price with the change on 9/8/2002.
> The price I would look for here would be 600.
> The only solution I can think of is to increment the date and query
> each date.
>
> I am not sure if there would be a better table design, but I would
> like to be able to do this with one query. Also, because the price
> does not change from year to year, is it possible to only comare by
> month and date? Not sure if this is very clear. I can expand more or
> answer any questions. Thank you very much for your help.

Surely this can be written in one query, and surely you can do this even if you change the table design, so that you only have date and month. The columns would then be char(4) have the format MMDD.

But, hm, I'm too lazy to think of it now. And I'm sure Steve Kass will love to take a tack on this.

Instead I have another suggestion. Whether its feasible depends a little. But say you need to this in many places in your application. In such case, the best design may be to have a row for each day of the year with the price for the widget. Yes, the table will take up a lot of space. But the queries will be simple to write and maintain.

-- 
Erland Sommarskog, SQL Server MVP
sommar_at_algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp