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.
| Author |
Topic |
|
steve_c
Yak Posting Veteran
54 Posts |
 Posted - 2008-05-16 : 16:55:52
|
| Hi all - I have a table of bookings:bookingiddatefromdatetoI want to be able to check if a new booking overlaps any others. I will pass in @datefrom and @dateto.Can anyone point me in the right direction of creating a query which will produce a list of any overlapping bookings?Thanks in advance all!Stephen. |
|
|
nathans
Aged Yak Warrior
938 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-17 : 03:03:55
|
quote:
Originally posted by steve_c
Hi all - I have a table of bookings:bookingiddatefromdatetoI want to be able to check if a new booking overlaps any others. I will pass in @datefrom and @dateto.Can anyone point me in the right direction of creating a query which will produce a list of any overlapping bookings?Thanks in advance all!Stephen.
IF EXISTS (SELECT * FROM BookingTable--booking started before and continuing after currently booked time WHERE DATEDIFF(ss,datefrom,@datefrom) >0 AND DATEDIFF(ss,@dateto,dateto) >0 )OR EXISTS (SELECT * FROM BookingTable--A booking starting in b/w currently booked slot WHERE DATEDIFF(ss,@datefrom,datefrom) >0 AND DATEDIFF(ss,datefrom,@dateto) >0)OR EXISTS (SELECT * FROM BookingTable--an earlier booking ending in b/w current slot WHERE DATEDIFF(ss,@datefrom,dateto) >0 AND DATEDIFF(ss,dateto,@dateto) >0)PRINT 'Booking Overlap' You can put this code in a INSTEAD OF INSERT TRIGGER so that it checs this condition and performs insert only if if does not fall into any of these |
 |
|
|
|
|
|
|
|
Quick one - booking dates