It was sent to cf-sql.. We're using SQLServer 2K
-----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, June 9, 2003 13:10 To: CF-Talk Subject: Re: 0ut 0f 0ffice Dates Mike Townend wrote: > > Sorry for the x-post x-posted to what? > I was wondering if anyone has an idea as to how I can do this. > Currently I have a task system whereby you can allocate jobs to users, > we have a table that can record that you are 0ut of the 0ffice for a > specified time, I need to workout if a user is 0ut of the 0ffice > between Now() and a deadline so that an administrator can be > informed... Currently I have this as a query > > SELECT * > FROM 0ut0f0ffice > WHERE UserID = 6 > AND (0ut0f0fficeFrom <= {d '2003-06-06'} > AND > 0ut0f0fficeTo >= {d '2003-06-10'}) > > > Now this works if you are 0ut of the 0ffice between now and the > deadline in one go. > > i.e. 05/06/2003 - 13/06/2003 - Seminar. > > But if a person sets up their out of office like this: > > 05/06/2003 - 08/06/2003 - Seminar 1 > 09/06/2003 - 13/06/2003 - Seminar 1 > > They are 0ut of the 0ffice and so cant get the task done, but the > above query wont pick this up therefore cannot inform anyone. > > Has any one got any ideas as to how to tackle this? Does your database support the OVERLAPS predicate? Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
