Re: Database Design and Query

By the way, I ignored the WidgetID, since I wasn't sure exactly how it related to anything else.

SK

Erland Sommarskog wrote:

> 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
Received on Fri Aug 09 2002 - 06:18:43 CEST