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

Help with a query

Guys-

Any help with this query is greatly appreciated...

DECLARE @.input TABLE
(NodeId VARCHAR(10),
IsChecked CHAR(1))

DECLARE @.Actual TABLE
(AndSetId INT,
NodeId VARCHAR(10),
IsChecked CHAR(1))

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')

-- We should get back 1, 2, 3, 4. We should not get back 5, 6, 7Did your instructor include the rules (the logic) behind this, or only give you the two inputs and the output that they expected? Have you recently covered anything like this in class?

-PatP|||Pat-

My apologies for not elaborating enough, what I was looking for.

Get all AndSetIds from @.Actual where

if NodeId is present in @.input then the isChecked should match.
eg: AndSetId 1
It has 3 records. And the records match with the records
in Input.
So 1 should be returned.

Where as let us look at AndSet 5.
It has NodeId 'e' as 'F' where as the input has 'e' as 'T'
So 5 should not be returned.

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')|||Never mind. I think I have a solution. Thanks for your time Pat.sql