Re: Database Design and Query

From: John Gilson <jag_at_acm.org>
Date: Thu, 08 Aug 2002 01:43:16 GMT
Message-ID: <U0k49.188915$QD2.42198900@twister.nyc.rr.com>

Since you seem to be assuming that you are counting all days, as opposed to just weekdays or business days, I'll assume that you don't necessarily have a table that enumerates all days in a standard (Gregorian) calendar. Joe Celko's solution succinctly covers that case.

I'm also assuming that the day count doesn't include the start date, that is 20020901 to 20020920 is 19 days and not 20 days.

My code is listed below. If you populate the table like so

INSERT INTO WidgetPrices
VALUES (1, '20020101', '20020415', 25) INSERT INTO WidgetPrices
VALUES (1, '20020416', '20020907', 50) INSERT INTO WidgetPrices
VALUES (1, '20020908', '20021231', 30) then you can get the price of widget 1 in the date range 20020901 to 20020920 by calling

EXEC getPrice 1, '20020901', '2002920'

which returns the table

WidgetID    ShipDate            ReturnDate        TotalDays    TotalPrice
1                 2002-09-01       2002-09-20      19                690

This price is calculated as (6 days * 50 + 13 days * 30). Hope this helps.

jag

*** Begin Code Listing ***

IF NULLIF(OBJECT_ID('WidgetPrices'), 0) > 0

DROP TABLE WidgetPrices
go
CREATE TABLE WidgetPrices
(

WidgetID        INT             NOT NULL,
ShipDate        SMALLDATETIME   NOT NULL,
ReturnDate      SMALLDATETIME   NOT NULL,
Price           INT             NOT NULL CHECK (Price > 0),

CONSTRAINT WidgetPrices_PK PRIMARY KEY (WidgetID, ShipDate), CONSTRAINT Dates_CK CHECK (ShipDate < ReturnDate) -- basic check )
go

IF NULLIF(OBJECT_ID('getPrice'), 0) > 0

DROP PROC getPrice
go
CREATE PROC getPrice

@widgetID INT,
@shipDate SMALLDATETIME,
@returnDate SMALLDATETIME

AS
IF @shipDate >= @returnDate

RETURN

SELECT @widgetID AS WidgetID,
       @shipDate AS ShipDate,
       @returnDate AS ReturnDate,
       SUM(Days) AS TotalDays,
       SUM(TotalPrice) AS TotalPrice
FROM (-- All ranges totally overlapped
      SELECT WidgetID,
             ShipDate,
             ReturnDate,
             Price,
             CASE WHEN ShipDate = @shipDate
                  THEN DATEDIFF(Dy, ShipDate, ReturnDate)
                  ELSE DATEDIFF(Dy, ShipDate, ReturnDate) + 1
             END AS Days,
             CASE WHEN ShipDate = @shipDate
                  THEN Price * DATEDIFF(Dy, ShipDate, ReturnDate)
                  ELSE Price * (DATEDIFF(Dy, ShipDate, ReturnDate) + 1)
             END AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = @widgetID AND
            ShipDate >= @shipDate AND
            ReturnDate <= @returnDate
      UNION ALL


      -- Range that is partially overlapped from within to end of range

      -- If such a range exists it's the leading range

      SELECT WidgetID,
             @shipDate AS ShipDate,
             ReturnDate,
             Price,
             DATEDIFF(Dy, @shipDate, ReturnDate) AS Days,
             Price * DATEDIFF(Dy, @shipDate, ReturnDate) AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = @widgetID AND
            ShipDate < @shipDate AND
            ReturnDate >= @shipDate AND
            ReturnDate <= @returnDate
      UNION ALL


      -- Range that is partially overlapped from start of range up to, but

      -- not including, end

      -- If such a range exists it's the trailing range

      SELECT WidgetID,
             ShipDate,
             @returnDate AS ReturnDate,
             Price,
             CASE WHEN ShipDate = @shipDate
                  THEN DATEDIFF(Dy, ShipDate, @returnDate)
                  ELSE DATEDIFF(Dy, ShipDate, @returnDate) + 1
             END AS Days,
             CASE WHEN ShipDate = @shipDate
                  THEN Price * DATEDIFF(Dy, ShipDate, @returnDate)
                  ELSE Price * (DATEDIFF(Dy, ShipDate, @returnDate) + 1)
             END AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = @widgetID AND
            ReturnDate > @returnDate AND
            ShipDate >= @shipDate AND
            ShipDate <= @returnDate) AS Ranges

*** End Code Listing ***

"Andy" <andrewngrimes_at_hotmail.com> wrote in message news: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 - 20:43:16 CDT