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_at_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

_at_widgetID INT,
_at_shipDate SMALLDATETIME,
_at_returnDate SMALLDATETIME

AS
IF _at_shipDate >= @returnDate

   RETURN

SELECT _at_widgetID AS WidgetID,
       _at_shipDate AS ShipDate,
       _at_returnDate AS ReturnDate,
       SUM(Days) AS TotalDays,
       SUM(TotalPrice) AS TotalPrice
FROM (-- All ranges totally overlapped
      SELECT WidgetID,
             ShipDate,
             ReturnDate,
             Price,
             CASE WHEN ShipDate = _at_shipDate
                  THEN DATEDIFF(Dy, ShipDate, ReturnDate)
                  ELSE DATEDIFF(Dy, ShipDate, ReturnDate) + 1
             END AS Days,
             CASE WHEN ShipDate = _at_shipDate
                  THEN Price * DATEDIFF(Dy, ShipDate, ReturnDate)
                  ELSE Price * (DATEDIFF(Dy, ShipDate, ReturnDate) + 1)
             END AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = _at_widgetID AND
            ShipDate >= _at_shipDate AND
            ReturnDate <= _at_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,
             _at_shipDate AS ShipDate,
             ReturnDate,
             Price,
             DATEDIFF(Dy, _at_shipDate, ReturnDate) AS Days,
             Price * DATEDIFF(Dy, _at_shipDate, ReturnDate) AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = _at_widgetID AND
            ShipDate < _at_shipDate AND
            ReturnDate >= _at_shipDate AND
            ReturnDate <= _at_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,
             _at_returnDate AS ReturnDate,
             Price,
             CASE WHEN ShipDate = _at_shipDate
                  THEN DATEDIFF(Dy, ShipDate, _at_returnDate)
                  ELSE DATEDIFF(Dy, ShipDate, _at_returnDate) + 1
             END AS Days,
             CASE WHEN ShipDate = _at_shipDate
                  THEN Price * DATEDIFF(Dy, ShipDate, _at_returnDate)
                  ELSE Price * (DATEDIFF(Dy, ShipDate, _at_returnDate) + 1)
             END AS TotalPrice
      FROM WidgetPrices
      WHERE WidgetID = _at_widgetID AND
            ReturnDate > _at_returnDate AND
            ShipDate >= _at_shipDate AND
            ShipDate <= _at_returnDate) AS Ranges

go

• *** 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 Thu Aug 08 2002 - 03:43:16 CEST