how to compare one time span to another one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to compare one time span to another one

Author

Topic

dia1234
Starting Member

12 Posts

Posted - 2013-11-18 : 03:18:01

I have to compare two time duratiion for a resource booking system. for example if a resource is booked for 9.00am to 2.00am ,it must not be booked during that duration and other coinciding times e.g 1.00pm to 2.00 pm should not be booked or even 9.00 am to 5.00pm should not be booked.

how can i write a sql query for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 05:26:48

look for non overlapping date range condition before blocking a booking.
ie something like


SELECT @Start,@End
WHERE NOT EXISTS (SELECT 1
FROM Booking 
WHERE ((@Start BETWEEN StartDate AND ENDDate)
OR(@End BETWEEN StartDate AND ENDDate))

WHERE @Start,@End are time slots between which you're trying to book
AND StartDate ENddate are fields in the table indicating existing bookings

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Prav4u
Starting Member

15 Posts

Posted - 2013-11-18 : 08:24:45

Create Table #t (BookingDate DateTime , bookingStartTime time , bookingEndTime time)
INSERT INTO #t values('15-nov-2013' , '09:00:00' , '14:00:00')
Declare @newbookingstart time , @newbookingEnd time -- pass timeband here also you can pass date parameter

IF Exists (Select * from #t where BookingDate = '15-nov-2013'
and ( @newbookingstart between bookingStartTime and bookingEndTime or @newbookingend between bookingStartTime and bookingEndTime
OR (@newbookingstart < bookingStartTime AND @newbookingEnd > bookingEndTime )))

Print 'Can Not book'
Else
Print 'Can book'

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-18 : 13:30:03

SELECT *
FROM dbo.Bookings
WHERE @StartTime > EndTime OR @EndTime < StartTime

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources