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 DATETIME Query

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.

SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date,
'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS
Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND
B.Booking_Status <> '1' AND NOT ( '2003-10-07' <
DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' >
DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )

Thanks for your helpYou can do this using a Calendar table:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY)

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

And a Numbers table: http://tinyurl.com/pta3

Here's a query which will work for any specified range of dates in the
Calendar table:

SELECT C1.caldate, 8.5 - COALESCE(A.Booked_Hours,0) AS Available_Hours
FROM Calendar AS C1
LEFT JOIN
(SELECT C2.caldate,
CAST(COUNT(DISTINCT DATEADD(MINUTE,N.num,C2.caldate))
/60.0 AS DECIMAL(4,2)) AS Booked_Hours
FROM Calendar AS C2
JOIN Numbers AS N
ON N.num BETWEEN 540 AND 1049
JOIN WMS_Bookings AS W
ON DATEADD(MINUTE,N.num,C2.caldate) >= W.Booking_Start_Date
AND DATEADD(MINUTE,N.num,C2.caldate) < W.Booking_End_Date
AND
((W.Booking_Start_Date>=C2.caldate
AND W.Booking_Start_Date < DATEADD(DAY,1,C2.caldate))
OR
(W.Booking_End_Date>=C2.caldate
AND W.Booking_End_Date < DATEADD(DAY,1,C2.caldate)))
GROUP BY C2.caldate) AS A
ON C1.caldate = A.caldate
WHERE C1.caldate BETWEEN '20030101' AND '20030131'

Note the redundant predicates in the derived table's WHERE clause. They help
improve the join performance. If overlapping bookings do not occur in your
system then you can remove DISTINCT from the query to improve performance
further.

--
David Portas
----
Please reply only to the newsgroup
--

Help with 2 datetime fields-1 stores date, the other time

Hi,
We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
other the time.
example query:

select aud_dt, aud_tm
from orders

results:
aud_dt aud_tm
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287

I'm trying to create a query that give me records from the current date in
the past hour.
Here's a script that gives me todays date but I cannot figure out the time:

select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
getdate())
from orders
where (datediff(d,aud_dt,getdate()) = 0)

results:
aud_dt aud_tm
datediff(0=today) timediff (since 1900-01-01)
2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
55978689

I added this next part to the above query but it does not work since the
date/time is from 1900-01-01
and (datediff(mi, aud_tm, getdate()) <= 60)

Thanks for any help.rdraider wrote:
> Hi,
> We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
> other the time.
> example query:
> select aud_dt, aud_tm
> from orders
> results:
> aud_dt aud_tm
> 2006-06-08 00:00:00.000 1900-01-01 12:32:26.287
> I'm trying to create a query that give me records from the current date in
> the past hour.
> Here's a script that gives me todays date but I cannot figure out the time:
> select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
> getdate())
> from orders
> where (datediff(d,aud_dt,getdate()) = 0)
> results:
> aud_dt aud_tm
> datediff(0=today) timediff (since 1900-01-01)
> 2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
> 55978689
>
> I added this next part to the above query but it does not work since the
> date/time is from 1900-01-01
> and (datediff(mi, aud_tm, getdate()) <= 60)
>
> Thanks for any help.

The correct way would be to fix the database and use one datetime
column. I'll assume you already know this and that it isn't possible
for some reason.

So, if you want to combine those two into one datetime field (which you
could then use in a query however you like) you can use something like
this:

cast((cast(aud_dt as float) + cast(aud_tm as float)) as datetime)

although you might lose some precision in the miliseconds. If that's
unacceptable, you can instead do this:

convert(datetime, convert(varchar(10), aud_dt, 1) + ' ' +
convert(varchar(10), aud_tm, 14))|||These both work well. Miliseconds don't matter.

Thank you.

"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1149832253.251499.139790@.h76g2000cwa.googlegr oups.com...
> rdraider wrote:
>> Hi,
>> We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the
>> other the time.
>> example query:
>>
>> select aud_dt, aud_tm
>> from orders
>>
>> results:
>> aud_dt aud_tm
>> 2006-06-08 00:00:00.000 1900-01-01 12:32:26.287
>>
>> I'm trying to create a query that give me records from the current date
>> in
>> the past hour.
>> Here's a script that gives me todays date but I cannot figure out the
>> time:
>>
>> select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,
>> getdate())
>> from orders
>> where (datediff(d,aud_dt,getdate()) = 0)
>>
>> results:
>> aud_dt aud_tm
>> datediff(0=today) timediff (since 1900-01-01)
>> 2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 0
>> 55978689
>>
>>
>> I added this next part to the above query but it does not work since the
>> date/time is from 1900-01-01
>> and (datediff(mi, aud_tm, getdate()) <= 60)
>>
>>
>> Thanks for any help.
> The correct way would be to fix the database and use one datetime
> column. I'll assume you already know this and that it isn't possible
> for some reason.
> So, if you want to combine those two into one datetime field (which you
> could then use in a query however you like) you can use something like
> this:
> cast((cast(aud_dt as float) + cast(aud_tm as float)) as datetime)
> although you might lose some precision in the miliseconds. If that's
> unacceptable, you can instead do this:
> convert(datetime, convert(varchar(10), aud_dt, 1) + ' ' +
> convert(varchar(10), aud_tm, 14))