473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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!
Jul 20 '05 #1
6 1439
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=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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
> 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
Jul 20 '05 #3
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!
Jul 20 '05 #4


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!
Jul 20 '05 #5
> 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)
)
)
Jul 20 '05 #6
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
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Dave | last post by:
After following Microsofts admonition to reformat my system before doing a final compilation of my app I got many warnings/errors upon compiling an rtf file created in word. I used the Help...
9
by: Tom | last post by:
A question for gui application programmers. . . I 've got some GUI programs, written in Python/wxPython, and I've got a help button and a help menu item. Also, I've got a compiled file made with...
6
by: wukexin | last post by:
Help me, good men. I find mang books that introduce bit "mang header files",they talk too bit,in fact it is my too fool, I don't learn it, I have do a test program, but I have no correct doing...
3
by: Colin J. Williams | last post by:
Python advertises some basic service: C:\Python24>python Python 2.4.1 (#65, Mar 30 2005, 09:13:57) on win32 Type "help", "copyright", "credits" or "license" for more information. >>> With...
7
by: Corepaul | last post by:
Missing Help Files When I enter "recordset" as the keyword and search the Visual Basic Help index, I get many topics of interest in the resulting list. But there isn't any information available...
5
by: Steve | last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp My expectation is that a developer using my DLL would be able to access this help file during his development time...
8
by: Mark | last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my home computer has an abbreviated help screen not 2% of the help on my laptop. All the settings look the same on both...
10
by: JonathanOrlev | last post by:
Hello everybody, I wrote this comment in another message of mine, but decided to post it again as a standalone message. I think that Microsoft's Office 2003 help system is horrible, probably...
1
by: trunxnirvana007 | last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"' 'UPGRADE_WARNING: Couldn't resolve...
0
by: hitencontractor | last post by:
I am working on .NET Version 2003 making an SDI application that calls MS Excel 2003. I added a menu item called "MyApp Help" in the end of the menu bar to show Help-> About. The application...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.