date/time overlapping - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

date/time overlapping

Author

Topic

ann9
Starting Member

2 Posts

Posted - 2009-04-17 : 05:27:29

Hi!
Does anyone know how to find dates/time overlapping?
I have a booking system with many resources. But the same resource can be booked by different people at the same time. So, how should I check whether any resource has date/time overlapping in the system?

Also, resources can be part of package, so I sould check resources and also resources which are part of package.

SELECT booking_start, booking_end,booking_id, resource_id, B.booking_id, R.resource_id
FROM BOOKING B,RESOURCE R, BOOKED_RESOURCE BR, PACKAGE_ITEM PI
WHERE ...;

So, how should I finished it? Does anyone know? Please, help me if you can!

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-17 : 05:43:53

Hi,

table Booking
(bookingId,fromDate,toDate)

you can use these where clause to check any time overlap where @frmDt and @toDt are the values to be inserted or updated in the table

where (@frmDt between fromDate and toDate)
or (@toDt between fromDate and toDate)
or(fromdate between @frmDt)
or (toDate between @frmDt)

Kunal

ann9
Starting Member

2 Posts

Posted - 2009-04-17 : 06:05:23

Thank you for your answer!

But in my case I have booking_start and booking_end, do you mean that I need to create 2 colomns that will check what is inserted and updated? I cannot realise how can I do it? How to add these colomns?
I do not quite understand how it will solve the problem...

SELECT booking_start, booking_end,booking_id, resource_id, B.booking_id, R.resource_id
FROM BOOKING B,RESOURCE R, BOOKED_RESOURCE BR, PACKAGE_ITEM PI
WHERE ...;

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-20 : 00:45:08

Hi
Here @frmDt and @toDt are the values to be inserted or updated in the table

and fromDate,toDate are the table columns

Kunal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 03:06:15

quote:
Originally posted by kunal.mehta

where (@frmDt between fromDate and toDate)
or (@toDt between fromDate and toDate)
or(fromdate between @frmDt)
or (toDate between @frmDt)

I don't believe that is a valid syntax.

SELECT *
FROM Table1
WHERE FromDate <= @WantedToDate
AND ToDate >= @WantedFromDate

E 12°55'05.63"
N 56°04'39.26"

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-20 : 05:37:26

Hi,

this is a valid syntex. I have used this in one of my projects and will check all schenarios.

query provided by Peso will fail in certain cases

Kunal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:42:00

quote:
Originally posted by kunal.mehta

this is a valid syntex.

I am referring to the last two lines

or (fromdate between @frmDt)
or (toDate between @frmDt)

They don't pass my parser.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 05:42:31

quote:
Originally posted by kunal.mehta

query provided by Peso will fail in certain cases

Which are those cases?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources