Database Design and Query
From: Andy <andrewngrimes_at_hotmail.com>
Date: 7 Aug 2002 13:52:15 -0700
Message-ID: <d420a4f5.0208071252.30038129_at_posting.google.com>
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.
Date: 7 Aug 2002 13:52:15 -0700
Message-ID: <d420a4f5.0208071252.30038129_at_posting.google.com>
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.Andy Received on Wed Aug 07 2002 - 22:52:15 CEST