SQL Server 2005. DateTime Comparision of two columns of same table

Question

I am having a query, I had a table named tblBooking There exist 3 columns in that table Column Names BookingID StartTime EndTime Datatye int datetime datetime Sample data 1 10:00 11:00 2 11:00 12:00 3 01:30 03:30

I need to compare values of two columns StartTime EndTime Query is : I had to alloacte a resource eg. Meeting Room This can be done on daily basis between 10:00 am to 7:00 pm. So I need to check the availibility of Meeting room on basis of Time. Considering all scenarios. This was a question asked to me in an interview which I ws u nable to answer so I need to find the way hw to do it

Answer 1

You can have multiple criteria in the where clause...
BETWEEN can compare multiple dates and times
For Example:

where requestedStartTime not between startTime and endTime
and requestedEndTime not between startTime and endTime

Answer 2

I am also havind same problem

Answer 3

Have a look at this example. You newed to check for overlapping periods, so the select with date times where there is no result will be correct.

        |---|           PERIOD
--------------------------------------
|---|                  FINE
    |---|               FINE
    |------|            NOT FINE
        |---|           NOT FINE
         |-|            NOT FINE
           |------|     NOT FINE
            |---|       FINE
                |---|   FINE
    |-------------|     NOT FINE




DECLARE @tblBookings TABLE(
        BookingID INT,
        StartTime DATETIME,
        EndTime DATETIME
)

INSERT INTO @tblBookings SELECT 1, '01 Jan 2010 10:00:00', '01 Jan 2010 11:00:00'
INSERT INTO @tblBookings SELECT 2, '01 Jan 2010 11:00:00', '01 Jan 2010 12:00:00'

DECLARE @StartDate DATETIME,
        @EndDate DATETIME

--Inside        
SELECT  @StartDate = '01 Jan 2010 10:30:00',
        @EndDate = '01 Jan 2010 11:30:00'

SELECT  *
FROM    @tblBookings
WHERE   NOT( @EndDate <= StartTime OR @StartDate >= EndTime)

--Overlapping left      
SELECT  @StartDate = '01 Jan 2010 09:30:00',
        @EndDate = '01 Jan 2010 10:30:00'

SELECT  *
FROM    @tblBookings
WHERE   NOT( @EndDate <= StartTime OR @StartDate >= EndTime)

--Overlapping right     
SELECT  @StartDate = '01 Jan 2010 11:30:00',
        @EndDate = '01 Jan 2010 12:30:00'

SELECT  *
FROM    @tblBookings
WHERE   NOT( @EndDate <= StartTime OR @StartDate >= EndTime)

--Overlapping left and right
SELECT  @StartDate = '01 Jan 2010 09:30:00',
        @EndDate = '01 Jan 2010 12:30:00'

SELECT  *
FROM    @tblBookings
WHERE   NOT( @EndDate <= StartTime OR @StartDate >= EndTime)

--THIS ONE IS FINE
SELECT  @StartDate = '01 Jan 2010 09:00:00',
        @EndDate = '01 Jan 2010 10:00:00'

SELECT  *
FROM    @tblBookings
WHERE   NOT( @EndDate <= StartTime OR @StartDate >= EndTime)

Answer 4

you can find all the "collisions" between the given date/time range and all the existing date/time ranges in your table with a query like this:

DECLARE @YourTable table (BookingID int,StartTime datetime,EndTime datetime)

INSERT INTO @YourTable VALUES (1, '2010-03-03 10:00am', '2010-03-03 10:30am')
INSERT INTO @YourTable VALUES (2, '2010-03-03 11:30am', '2010-03-03 01:30pm')

DECLARE @StartDate datetime
       ,@EndDate datetime

SELECT @StartDate='2010-03-03 9:00am'
      ,@EndDate='2010-03-03 10:15am'
;WITH AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DATEADD(mi,30,DateOf) --smallest increment to consider is 30 minutes
        FROM AllDates
    WHERE DateOf<@EndDate
)
,Collisions AS
(SELECT
     * 
     FROM @YourTable         y
         INNER JOIN AllDates a ON a.DateOf>=y.StartTime AND a.DateOf<=y.EndTime
)
SELECT * FROM Collisions

OUTPUT:

BookingID StartTime               EndTime                 DateOf
--------- ----------------------- ----------------------- -----------------------
1         2010-03-03 10:00:00.000 2010-03-03 10:30:00.000 2010-03-03 10:00:00.000
1         2010-03-03 10:00:00.000 2010-03-03 10:30:00.000 2010-03-03 10:30:00.000

(2 row(s) affected)