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=subscribe&forumid=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
