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=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 0ut 0f 0ffice Dates
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=subscribeforumid=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
Re: 0ut 0f 0ffice Dates
Mike Townend wrote:
We're using SQLServer 2K
If it support OVERLAPS (check the manual) it would be something like:
WHERE UserID = 6
AND (0ut0f0fficeFrom,0ut0f0fficeTo) OVERLAPS ('2003-06-06','2003-06-10')
Else, how about:
SELECT *
FROM 0ut0f0ffice
WHERE UserID = 6
AND 0ut0f0fficeFrom BETWEEN '2003-06-06' AND '2003-06-10'
OR 0ut0f0fficeTo BETWEEN '2003-06-06' AND '2003-06-10'
Jochem
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 0ut 0f 0ffice Dates
Ok, I now have
DECLARE @Now AS DateTime
DECLARE @DeadLine AS DateTime
DECLARE @UserID AS Int
SET @Now = {d '2003-05-15'}
SET @Deadline = {d '2003-05-25'}
SET @UserID = 6
SELECT *
FROM OutOfOffice
WHERE UserID = @UserID
AND (
(OutOfOfficeTo BETWEEN @Now AND @DeadLine)
OR
(OutOfOfficeFrom BETWEEN @Now AND @DeadLine)
)
Which gets me all OOO periods between 2 dates, what I need to do now is
check that if there is more than one, that the dates are sequential is this
possible? Or should I do the processing on the CF side?
-Original Message-
From: Mike Townend [mailto:[EMAIL PROTECTED]
Sent: Monday, June 9, 2003 13:25
To: CF-Talk
Subject: RE: 0ut 0f 0ffice Dates
It was sent to cf-sql..
We're using SQLServer 2K
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: 0ut 0f 0ffice Dates
Mike Townend wrote: SELECT * FROM OutOfOffice WHERE UserID = @UserID AND ( (OutOfOfficeTo BETWEEN @Now AND @DeadLine) OR (OutOfOfficeFrom BETWEEN @Now AND @DeadLine) ) Which gets me all OOO periods between 2 dates, what I need to do now is check that if there is more than one, that the dates are sequential is this possible? Or should I do the processing on the CF side? How about checking that the number of days somebody is OOO is equal to the number of days between now and the deadline? If that serves your purpose, use the query below (check the datediff syntax, I don't use non-standard date functionality very often). SELECT SUM(DateDiff(d,MAX(OutOfOfficeFrom, @Now),Min(OutOfOfficeTo, @DeadLine))) FROM OutOfOffice WHERE UserID = @UserID AND ( (OutOfOfficeTo BETWEEN @Now AND @DeadLine) OR (OutOfOfficeFrom BETWEEN @Now AND @DeadLine) ) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 0ut 0f 0ffice Dates
Like the sound of the logic, however the code doesn't run on SQL2K as the Max (and therefore Min) functions only take the one parameter and so is not running :( -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, June 9, 2003 14:26 To: CF-Talk Subject: Re: 0ut 0f 0ffice Dates How about checking that the number of days somebody is OOO is equal to the number of days between now and the deadline? If that serves your purpose, use the query below (check the datediff syntax, I don't use non-standard date functionality very often). SELECT SUM(DateDiff(d,MAX(OutOfOfficeFrom, @Now),Min(OutOfOfficeTo, @DeadLine))) FROM OutOfOffice WHERE UserID = @UserID AND ( (OutOfOfficeTo BETWEEN @Now AND @DeadLine) OR (OutOfOfficeFrom BETWEEN @Now AND @DeadLine) ) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: 0ut 0f 0ffice Dates
Mike Townend wrote: Like the sound of the logic, however the code doesn't run on SQL2K as the Max (and therefore Min) functions only take the one parameter and so is not running :( Use CASE. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: 0ut 0f 0ffice Dates
Thanks very much, what I have at the moment is
DECLARE @Now AS DateTime
DECLARE @DeadLine AS DateTime
DECLARE @UserID AS Int
SET @Now = {d '2003-05-15'}
SET @Deadline = {d '2003-05-25'}
SET @UserID = 6
SELECT SUM(DateDiff(d, CASE WHEN OutOfOfficeFrom = @Now THEN
OutOfOfficeFrom WHEN @Now = OutOfOfficeFrom THEN @Now END, CASE WHEN
OutOfOfficeTo = @DeadLine THEN OutOfOfficeTo WHEN @DeadLine =
OutOfOfficeTo THEN @DeadLine END)) AS iDaysOut,
DateDiff(d, @Now, @DeadLine) AS iDifference
FROM OutOfOffice
WHERE UserID = @UserID
AND (
(OutOfOfficeTo BETWEEN @Now AND @DeadLine)
OR
(OutOfOfficeFrom BETWEEN @Now AND @DeadLine)
)
And this seems to be working :)
Thanks again Jochem
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Monday, June 9, 2003 14:42
To: CF-Talk
Subject: Re: 0ut 0f 0ffice Dates
Mike Townend wrote:
Like the sound of the logic, however the code doesn't run on SQL2K as
the Max (and therefore Min) functions only take the one parameter and
so is not running :(
Use CASE.
Jochem
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
