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=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
