Simple but tricky problem...

avdhut_s · December 5th, 2007, 11:21 AM

I've a serious problem...

1.Consider a Conference Room Booking scenario
where in room reservation is stored as two columns : DateTimeFrom & DateTimeTo.

2. Now, i want to compare a new user entered values; To & From dates with existing records,

if entered values lies between the time range of DateTimeFrom & DateTimeTo
then reject else Accept entry.

i.e.
If room is already reserved for time 2.00(DateTimeFrom ) to 6.00 (DateTimeTo);
then it shouldnt accept entry for time range say, 3.00 to 4.00

Table :
intRoomId| dtmStart | dtmEnd
__________________________________________________ ______
1 |2007-12-28 02:32:00 |2007-12-28 04:32:20
1 |2007-12-28 08:32:00 |2007-12-28 10:32:20

Now,
1. new Entry for time between 2.00 to 4.00 & 8.00 to 10.00 should be rejected.
2. new entry for time between entire slot say 1.00 to 11.00 should also be rejected.

i've tried this :

Code:


Select intID, 
SUM(CASE WHEN  CAST(@UserDateFrom AS DATETIME) >= dtmStart AND  CAST(@UserDateTo AS DATETIME) < dtmEnd
        THEN 1 ELSE 0 END)
from TestDates
group by intID

I've tried a lot but its not working out.. SQL pros..plz help
thanks in advance

rstelma · December 5th, 2007, 08:07 PM

Here's a suggestion that works in a query in my app:

SELECT dbo.getTime(InspStart) AS InspStart, dbo.getTime(InspEnd) AS InspEnd FROM Inspections WHERE RoomID = 22 AND
((InspStart < '12/5/2007 6:30:01 AM' AND '12/5/2007 6:30:01 AM' < InspEnd) OR (InspStart < '12/5/2007 2:00:01 PM' AND '12/5/2007 2:00:01 PM' < InspEnd) OR (InspStart = '12/5/2007 6:30:01 AM' AND InspEnd = '12/5/2007 2:00:01 PM')) ORDER BY WorkID;

You can study this query and see that it checks to see if the requested start and end times are within any start and end times, checks to see if the requested times overlap either end of an existing reservation. It also checks to see if either the requested start or end times equal any existing reservation times

avdhut_s · December 6th, 2007, 05:08 AM

Thanks for your suggestion.
But your query fails for one condition :

if room is booked from 2.00 to 4.00 and 8.00 to 10.00 ;
and
user sends time 1.00 to 11.00 ,that is entire time slot,
overriding previous reservation.

This is also a invalid entry & hence shouldn't be accepted.
query fails for this condition

waitin for your reply...

rstelma · December 6th, 2007, 01:10 PM

So what's the answer? I've given you a great head start. Even if you don't know the syntax, what's the logic? Thank you for pointing that out. My time system won't accept that time slot now.

Peso · December 6th, 2007, 04:45 PM

Either

SELECT *
FROM Table1
WHERE @WantedStart < dtmEnd AND @WantedEnd > dtmStart

SELECT *
FROM Table1
WHERE @WantedStart > dtmEnd OR @WantedEnd < dtmStart

avdhut_s · December 7th, 2007, 05:47 AM

@rstelma : Can you post your new query then

I've a cursor which handles all the possibilities..
If u have a better answer then please post it..

here's the working cursor:

Code:

CREATE PROC DateTimeSlotCURSOR 
@dtmUserFrom     DATETIME,
@dtmUserTo         DATETIME,
@intRoomID         INT
AS
BEGIN

    DECLARE @PFrom DATETIME
    DECLARE @PTo DATETIME

    DECLARE test CURSOR 
        FOR select dtmFrom,dtmTo FROM TestDates WHERE intRoomId = @intRoomID
    OPEN test
    FETCH NEXT FROM test INTO @PFrom,@PTo
    WHILE (@@FETCH_STATUS=0)
    BEGIN
        IF(@dtmUserFrom = @PFrom OR @dtmUserTo=@PTo)
        BEGIN
            PRINT 'Reserved'
            CLOSE test
            DEALLOCATE test
            RETURN 0
        END
        ELSE
        BEGIN
            IF(@PFrom > @dtmUserFrom AND @PFrom < @dtmUserTo OR @PTo > @dtmUserFrom AND @PTo < @dtmUserTo)
            BEGIN
                PRINT 'Reserved'
                CLOSE test
                DEALLOCATE test
                RETURN 0
            END
            ELSE
            BEGIN
                IF(@dtmUserFrom > @PFrom AND @dtmUserTo < @PTo)
                BEGIN
                    PRINT 'Reserved'
                    CLOSE test
                    DEALLOCATE test
                    RETURN 0
                END
            END
        END
    FETCH NEXT FROM test INTO @PFrom,@PTo
    END
    PRINT 'AVAILABLE'
    CLOSE test
    DEALLOCATE test
    RETURN 1
END

--EXEC DateTimeSlotCURSOR '2007-11-20 01:00:00','2007-11-20 10:00:00',1

rstelma · December 7th, 2007, 08:20 PM

You don't really need a cursor for this.

All I needed to change in my query above was:

OR (InspStart = '12/5/2007 6:30:01 AM' AND InspEnd = '12/5/2007 2:00:01 PM')

to:

OR (InspStart >= '12/5/2007 6:30:01 AM' AND InspEnd <= '12/5/2007 2:00:01 PM')

Hope this helps.

Richard

Jeff Moden · December 8th, 2007, 12:07 PM

Don't let Peso's deceptively simple solution pass you by because it IS the correct solution.

--Jeff Moden

avdhut_s · December 10th, 2007, 04:41 AM

@Jeff Moden
Yes Peso's solution is a correct solution , but its not a complete solution. It doesnt handle all the conditions that i have mentioned.

@rstelma
Your corrected query still doesnt give desired result for entire time slot.. Please go through it again...

I know cursor is not the best answer but currently its the only working solution that i have... any suggestions are most welcome...
thnx :)

Jeff Moden · December 10th, 2007, 10:05 AM

Quote:

quote:Originally posted by avdhut_s
@Jeff Moden
Yes Peso's solution is a correct solution , but its not a complete solution. It doesnt handle all the conditions that i have mentioned.

@rstelma
Your corrected query still doesnt give desired result for entire time slot.. Please go through it again...

I know cursor is not the best answer but currently its the only working solution that i have... any suggestions are most welcome...
thnx :)

Ok then... I suggest you study Peso's answer :D... and like any good programmer, adapt it to become the "complete" answer you want... his only intent was to show you the principle of the best method... you should be able to easily adapt it.

--Jeff Moden