Hi,

I have a Table with reservations for Northwind Products:

ReservationID
ProductID
StartDate
EndDate
Quantity

I need to tell how much Reservations are still possible in a given
TimeFrame. Is this possible without Cursor?

Here is a sample:

ID  StartDate    EndDate         Quantity
1   2005-10-10   2005-29-10      1
2   2005-25-10   2005-28-10      1
3   2005-20-10   2005-22-10      1
4   2005-10-06   2005-15-06      1
5   2005-20-12   2005-25-12      1

I need To get the Maximum Quantity of reserved Items from '2005-05-10'
until '2005-30-10'

declare @StartDate datetime
declare @EndDate datetime

SET @StartDate = '2005-05-10'
SET @EndDate = '2005-30-10'

SELECT * from RESERVATION WHERE
Not
(
StartDate <= @StartDate AND EndDate <= @EndDate
OR
StartDate >= @EndDate AND EndDate >= @EndDate
)
gives 3 Rows back (id 1-3) that are all in the given TimeFrame. The
problem is, that if I do sum(quantity) I get 3. But id 2 and 3 dont
overlap so the correct number of maximum  Reservations in this
Timeframe is 2.
Any ideas ?
regards Mathias

Can you explain this in a little more detail?

Thanks

Jerry

<mathiasfrit***@gmx.de> wrote in message
Show quoteHide quote

I know this is hard to see:
Lets assume I have 3 Products of type ProductID 1 at store.
A customer asks how much he can reserve from '2005-05-10'
until '2005-30-10' . Corect answer is:1.

Product 1 is used by ReservationID 1 from 10.10.-29.10.
Product 2 is used by ReservationID 3 from 20.10.- 22.10 an later by
reservationID 2  from 25.10.-28.10.
Product 3 is still available for the requested TimeSpan.

Anyway i didnt give the productid in the sample. It is always 1

ID  StartDate    EndDate         Quantity  ProductID
1   2005-10-10   2005-29-10      1           1
2   2005-25-10   2005-28-10      1           1
3   2005-20-10   2005-22-10      1           1
4   2005-10-06   2005-15-06      1           1
5   2005-20-12   2005-25-12      1           1

On 22 Oct 2005 14:59:49 -0700, mathiasfrit***@gmx.de wrote:

Show quoteHide quoteHi Mathias,

First: please try to accustom yourself to a different date format when
posting on Usenet. I can handle yyyymmdd, yyyy-mm-dd, dd/mm/yyyy,
mm/dd/yyyy. But yyyy-dd-mm is very unusual (IS it actually a defined
standard somewhere? If so, where??). And it looks too much like the ISO
standard format yyyy-mm-dd. This gets confusing.
Besides the only formats that are guanarteed unambiguously interpreted
by SQL Server are
* yyyymmdd (note: no dashes, slashes or other puctuation!);
* yyyy-mm-ddThh:mm:ss (note: dasheh between the date parts, colons
between the time parts, and an uppercase T to seperate them both);
* yyyy-mm-ddThh:mm:ss.mmm (same as above, but with milliseeconds).


Anyway, here's a query that will return you the maximum number of
reserved items in a specified period. You'll have to subtract this from
the total number of items at store yourself, since you didn't include
this info in the description you posted.

-- Create a table
CREATE TABLE #Reservations
           (ID int NOT NULL,
            StartDate smalldatetime NOT NULL,
            EndDate smalldatetime NOT NULL,
            Quantity int NOT NULL DEFAULT 1,
            ProductID int NOT NULL,
            PRIMARY KEY (ID),
         -- FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
            CHECK (EndDate >= StartDate),
            CHECK (Quantity >= 1),
           )
go
-- Add sample data
INSERT INTO #Reservations (ID, StartDate, EndDate, Quantity, ProductID)
SELECT 1, '20051010', '20051029', 1, 1
UNION ALL
SELECT 2, '20051025', '20051028', 1, 1
UNION ALL
SELECT 3, '20051020', '20051022', 1, 1
UNION ALL
SELECT 4, '20050610', '20050615', 1, 1
UNION ALL
SELECT 5, '20051220', '20051225', 1, 1
go
-- Define and fill variables for period and productID
DECLARE @StartDate smalldatetime,
        @EndDate smalldatetime,
        @ProductID int
SET     @StartDate = '20051005'
SET     @EndDate   = '20051030'
SET     @ProductID =  1

Thank You Hugo,
this works great. And next post will be in a  common DateTime format.
This helped me a lot.
regards Mathias

COM+ Applications hanging
Who Added Indexing to Table?
sql weird
NOLOCK
Retrieval of IDENTITY after "bulk" insert?