DateTime - overlaping Timespans

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> But id 2 and 3 dont
>overlap so the correct number of maximum Reservations in this
>Timeframe is 2.
Can you explain this in a little more detail?
Thanks
Jerry
<mathiasfritsch@.gmx.de> wrote in message
news:1129920891.505680.272210@.g43g2000cwa.googlegroups.com...
> 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
>|||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, mathiasfritsch@.gmx.de wrote:

>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
Hi 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
--
-- Here's the actual query
--
SELECT MAX(ReservedItems)
FROM (SELECT a.StartDate, SUM(b.Quantity) AS ReservedItems
FROM (SELECT StartDate
FROM #Reservations
WHERE StartDate BETWEEN @.StartDate AND @.EndDate
AND ProductID = @.ProductID
UNION
SELECT @.StartDate) AS a
INNER JOIN #Reservations AS b
ON b.ProductID = @.ProductID
AND b.StartDate <= a.StartDate
AND b.EndDate >= a.StartDate
GROUP BY a.StartDate) AS c
go
-- Mop up when we're done
DROP TABLE #Reservations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank You Hugo,
this works great. And next post will be in a common DateTime format.
This helped me a lot.
regards Mathias