Help with a SP

[Kieran Dutfield]

Code:
ALTER PROCEDURE GetBookedResour ce
(
@StartDate datetime,
@EndDate datetime,
@Resource char(30)
)
AS

SELECT *
FROM tblBookings
WHERE StartDate >= @StartDate and EndDate <= @EndDate and
Resource=@Resou rce
_______________ _______________ _______________ _______________ ____________
_______________ ______________
The SP I'm using above is used to find out if a resource is free for a
particular period of time.

The time at my workplace is split into 6 periods....

P1 starts at 9am and ends at 10am.....
P2 starts as 10.01am and ends at 11am...e.t.c.
When Bookings are added...I append the time to the Start/End Date
depending on what period the end user has selected..
e.g..
Quote:
If end user has booked a resource for today at P1 then I would have
appended 9am to StartDate and 10am to the EndDate before I pass both the
StartDate and EndDate to the SP.

If end user has booked a resource from today at P1 to tomorrow at P2
then I would have appended 9am to StartDate and 11am to the EndDate
before I pass both the StartDate and EndDate to the SP.

The Problem
The SP only returns the correct data when the @StartDate and @EndDate
are on the same day and period
e.g..
Quote:
@StartDate=Toda y 9AM and @EndDate=Today 10AM


It doesn't return the current data when @StartDate and @EndDate are on
the same day but on different periods
e.g..
Quote:
@StartDate=Toda y 9AM and @EndDate=Today 11AM

Nor does it return the correct data when @StartDate and @EndDAte on on
different Days
e.g.
Quote:
@StartDate=Toda y 9AM and @EndDate=Tomorr ow 11AM

Can anyone explain why that is??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Kieran Dutfield (ko****@talk21. com) writes:

The Problem
The SP only returns the correct data when the @StartDate and @EndDate
are on the same day and period
e.g..
Quote:
@StartDate=Toda y 9AM and @EndDate=Today 10AM

It doesn't return the current data when @StartDate and @EndDate are on
the same day but on different periods
e.g..

Quote:
@StartDate=Toda y 9AM and @EndDate=Today 11AM

Nor does it return the correct data when @StartDate and @EndDAte on on
different Days
e.g.
Quote:
@StartDate=Toda y 9AM and @EndDate=Tomorr ow 11AM

I think you need to provide more input. What does your actual calls
look like? To me it sounds it is when you add the time portions that
things go wrong.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[louis nguyen]

> SELECT *

FROM tblBookings
WHERE StartDate >= @StartDate and EndDate <= @EndDate and
Resource=@Resou rce

Given that tblBookings show when a resource is busy and has 3 fields:
RESOURCE, BSTART, & BEND. And you want to know if the resource is
free for a given period: @START & @END. Consider the cases below. A
resource is free if:

exists(select * from tblbookings where resource=@resou rce and
(datediff(minut e,@end,bstart)> =0 OR datediff(minute ,bend,@start) >=0))

a) Resource is busy
BStart Bend
-------|---------|
| |
@Start @End

b) Resource is busy
BStart Bend
-------|---------|
| |
@Start @End

c) Resource is busy
BStart Bend
-------|---------|
| |
@Start @End

d) Resource is free
BStart Bend
-------|---------|
| |
@Start@End

e) Resource is free
BStart Bend
-------|---------|
| |
@Start @End

[Kieran Dutfield]

Here is a link to another forum I sumitted the problem to...It gives a
bit more detail

http://www.vbcity.com/forums/topic.asp?tid=56539


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Kieran Dutfield]

I want to convert the period given into the appropiate datetime when it
is passed to the SP...
e.g. the below parameter value would be the value I would pass for 1st
Jan 2004 starting Period 1
Quote:
@StartDate=#1/1/2004 9:00:00 AM#
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[louis nguyen]

> SELECT *

FROM tblBookings
WHERE StartDate >= @StartDate and EndDate <= @EndDate and
Resource=@Resou rce

Hi,

Ok second attempt. I think I understand now that there are multiple
rows for each resource. Then change the where clause to identify if
resource is busy and then wrap it with a not exists.

not exists(
SELECT * FROM tblBookings
WHERE Resource=@Resou rce and (
(@startdate between startdate and enddate) or
(@enddate between startdate and enddate)
)
)

[Erland Sommarskog]

Kieran Dutfield (ko****@talk21. com) writes:

Here is a link to another forum I sumitted the problem to...It gives a
bit more detail

http://www.vbcity.com/forums/topic.asp?tid=56539

Tried the link, but it appears that I have to register to view it. So
you may prefer to repost the information here.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp