Path: news.easynews.com!easynews!ps01-sjc1!news.webusenet.com!newsfeed1.cidera.com!Cidera!cyclone.rdc-nyc.rr.com!news-out.nyc.rr.com!twister.nyc.rr.com.POSTED!not-for-mail Reply-To: "John Gilson" From: "John Gilson" Newsgroups: comp.databases.ms-sqlserver,comp.databases.theory References: Subject: Re: Database Design and Query Lines: 163 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Thu, 08 Aug 2002 01:43:16 GMT NNTP-Posting-Host: 66.108.53.96 X-Complaints-To: abuse@rr.com X-Trace: twister.nyc.rr.com 1028770996 66.108.53.96 (Wed, 07 Aug 2002 21:43:16 EDT) NNTP-Posting-Date: Wed, 07 Aug 2002 21:43:16 EDT Organization: Road Runner - NYC Xref: easynews comp.databases.ms-sqlserver:76342 comp.databases.theory:21992 X-Received-Date: Wed, 07 Aug 2002 18:43:07 MST (news.easynews.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 go -- *** End Code Listing *** "Andy" wrote in message news:d420a4f5.0208071252.30038129@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