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

Dates & Stored Procedure

Hi
I want to know how can i Pass tow Dates to Stored Procedure in sql server
2000 by using vb6.
here below my code i used northwind database ,when i run the code i got
error in the cmd.excute .
i thing my problem in passing date, can any when tell me what is the problem
Private Sub Command2_Click()
Call connect
Call setup
Dim cmd As New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Employee Sales by Country"
Dim parm As New ADODB.Parameter
Dim parm2 As New ADODB.Parameter
Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/1997")
Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/2005")
cmd.Parameters.Append parm
cmd.Parameters.Append parm2
Dim RST As New ADODB.Recordset
Set RST = cmd.Execute
Set DataGrid1.DataSource = RST
DataGrid1.ReBind
End SubWhat does "got error" mean? Could you tell us the exact error message, and
what line it corresponds to here?
On 3/20/05 11:55 PM, in article
D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com, "ayman"
<ayman@.discussions.microsoft.com> wrote:

> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the probl
em
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>|||Ayman,
The parameters for the sample procedure [Employee Sales by Country] are
named @.Beginning_Date and @.Ending_Date. You have named both of
your parameters ShippedDate. Could that be the problem?
Steve Kass
Drew University
ayman wrote:

>Hi
>I want to know how can i Pass tow Dates to Stored Procedure in sql server
>2000 by using vb6.
>here below my code i used northwind database ,when i run the code i got
>error in the cmd.excute .
>i thing my problem in passing date, can any when tell me what is the proble
m
>Private Sub Command2_Click()
>Call connect
>Call setup
>
>Dim cmd As New ADODB.Command
>cmd.ActiveConnection = Con
>cmd.CommandType = adCmdStoredProc
>cmd.CommandText = "Employee Sales by Country"
>Dim parm As New ADODB.Parameter
>Dim parm2 As New ADODB.Parameter
>Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/1997")
>Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/2005")
>cmd.Parameters.Append parm
>cmd.Parameters.Append parm2
>
>Dim RST As New ADODB.Recordset
>Set RST = cmd.Execute
>Set DataGrid1.DataSource = RST
>DataGrid1.ReBind
>End Sub
>
>
>|||ayman
Have you tried to format the dates as 'YYYYMMDD'?
"ayman" <ayman@.discussions.microsoft.com> wrote in message
news:D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com...
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the
problem
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>