Help with a SP

Code:
ALTER PROCEDURE GetBookedResource
(
@.StartDate datetime,
@.EndDate datetime,
@.Resource char(30)
)
AS

SELECT *
FROM tblBookings
WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
Resource=@.Resource

__________________________________________________ ______________________
_____________________________

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=Today 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=Today 9AM and @.EndDate=Today 11AM

Nor does it return the correct data when @.StartDate and @.EndDAte on on
different Days
e.g.

Quote:
@.StartDate=Today 9AM and @.EndDate=Tomorrow 11AM

Can anyone explain why that is??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Kieran Dutfield (kolo83@.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=Today 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=Today 9AM and @.EndDate=Today 11AM
> Nor does it return the correct data when @.StartDate and @.EndDAte on on
> different Days
> e.g.
>
> Quote:
> @.StartDate=Today 9AM and @.EndDate=Tomorrow 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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Resource=@.Resource

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=@.resource and
(datediff(minute,@.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|||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!|||
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!|||> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Resource=@.Resource

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=@.Resource and (
(@.startdate between startdate and enddate) or
(@.enddate between startdate and enddate)
)
)|||Kieran Dutfield (kolo83@.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, sommar@.algonet.se

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