Posted by Jerry Stuckle on 06/05/05 23:37

(Response limited to comp.lang.php)

Chris wrote:
> Having a little trouble returning the result I'm looking for in a
> query and thought I'd ask a question here.
>
> I have an input screen with a pull down menu to capture vacation
> activity within a given month as well as capture overlap onto another
> month. For example, if you're taking vacation between June 25 and
> July 4, the input would be 'junjul' if the days off were July 4
> through 10, the input would be 'jul' and if the days off happened to
> be July 25 through August 5, the input would fall under 'julaug' with
> the idea being that the query captures any vacation for the month of
> July but also sends back info for any overlapping month vacation.
> Also, if you did a query for the month of June or August, that
> overlapping vacation would show as well. I did this very effectively
> in Access once and am trying duplicate it with PHP.
>
> So a question becomes, IF jul (is selected in the query for the month
> of July) THEN return any data for junjul AND julaug if they were used
> on the input form and have that data returned under a July query.
>
> Any thoughts?

I think using the junjul/julaug/whatever will complicate matters. For
instance 0 my wife's ex-AA just had a baby and is taking 3 months off.
And my wife has > 2 months of vacation signed up. If you use
junjul/julaug, what about the person who takes an extended vacation such
as May 31 - August 2? Or even May 30 - Sept. 1?

To resolve everything, use a separate start date and end data, Then,
you need these tests:

1. start date >= 07/01 and start data <= 07/31 (or < 08/01 which
might be easier), or
2. end data - same as above, or
3. start date < 07/01 and end date > 07/31 (or >= 08/01)

Easily handled in SQL statements or date functions.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]