Re: Database Design and Query

Right you are. Here it is as a user-defined function, but the logic is simple enough that you can work this into a larger query easily if you need to. Basically you add up the costs associated with each row of WidgetPrice that overlaps the customer's date range.

CREATE TABLE [WidgetPrice] (

[WidgetID] [int] NOT NULL ,
[ShipDate] [smalldatetime] NOT NULL ,
[ReturnDate] [smalldatetime] NOT NULL ,
[Price] [int] NOT NULL ,
)

GO
set dateformat mdy

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)

GO

create function Price (
  _at_ShipDate smalldatetime,
  _at_ReturnDate smalldatetime
) returns int as begin
  set _at_ShipDate = @ShipDate + 1 -- Don't charge for the ship date   return (
    select sum(OnePrice) -- OnePrice is from one row of WidgetPrice     from (

      select Price * cast(
        case when ReturnDate - _at_ShipDate <= @ReturnDate - ShipDate
             then ReturnDate - _at_ShipDate
        else _at_ReturnDate - ShipDate end + 1
        -- case expression is days of overlap between price dates and customer dates
        as int) as OnePrice
      from WidgetPrice
      where _at_ShipDate <= ReturnDate
      and _at_ReturnDate >= ShipDate
      -- where condition is "price dates overlap customer dates"

select dbo.Price('20020901','20020920')

go
drop table WidgetPrice
drop function Price

Steve Kass
Drew University

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