Hi,

Background:

I've created a vacation-request application for our company's intranet. When an employee requests a vacation, he has to list the name of another employee who will 'fill in' for him while he is gone.

Before a vacation request can be saved in MySQL, I need to check to make sure that the person listed as the employee's replacement during this time has not already requested a vacation during the same time period...to check to be sure that the two vacation periods do not overlap.

Date Ranges:

I have fields saved in MySQL called unixStartDate & unixEndDate that are unix timestamps for the first day of the employee's vacation and the last day of the emplosyee's vacation.

I have tried to do a check using purely SQL, but this doesn't account for all possibilities of overlapping dates:

(unixStartDate is the unix timestamp for the replacement, whereas the PHP var $unixStartDate is the unix timestamp for the employee who wants to post a new vacation request)

$checkSQL = "SELECT * from $DB_TBLName WHERE
(Trim(WorkerEmail)='$userReplacementEmail')
AND (Status < 40)
AND
(
(
($unixStartDate = unixStartDate)
)
OR
(
($unixEndDate = unixEndDate)
)
OR
(
($unixStartDate = unixEndDate)
)
OR
(
($unixEndDate = unixStartDate)
)
OR
(
(unixStartDate < $unixStartDate) && (unixEndDate > $unixEndDate)
)
OR
(
(unixStartDate > $unixStartDate) && (unixEndDate < $unixEndDate)
)
)
";

So I think what I instead need to do is use PHP code instead of SQL to check for overlapping dates in the 2 date ranges I have.

My two ranges would be like this:

Replacement's Date Range:
$unixStartDateReplacementVacation
...to...
$unixEndDateReplacementVacation

Employee's Date Range:
$unixStartDateEmployeeVacation
...to..
$unixEndDateEmployeeVacation

and i need to check that none of the dates occuring in the first date range listed above appear in the second date range.

unfortunately, i have no basic idea of how i should go about doing this...should i use arrays of dates, for loops, or what?

thanks a whole lot in advance,

Tom

    You can do it in SQL, but I think you should simplify you query
    Look, you've got two range t1-t2 and T1-T2, So the two ranges do overlap if
    T2 >= t1 AND T1 <= t2

    Your query should be
    $checkSQL = "SELECT * from $DB_TBLName WHERE
    (Trim(WorkerEmail)='$userReplacementEmail')
    AND Status < 40
    AND unixEndDate >= $unixStartDate
    AND unixStartDate <= $unixEndDate

      wow, wien...thanks a lot!

      hard for me to believe that it's that easy. somehow, i have problems wrapping my head around the logic in this, but i've tried out your sql and it seems to work...so far i haven't found a single exception:

      $checkSQL = "SELECT * from $DB_TBLName WHERE
      (Trim(WorkerEmail)='$userReplacementEmail')
      AND (Status < 40)
      AND
      (
      unixReplacementVacationEndDate >= $unixStartDate
      AND
      unixReplacementVacationStartDate <= $unixEndDate
      )
      ";

      many thanks...i was going crazy with this :p

        Well, the logic is that if your vacation and mine don't overlap, then either you got back from yours before I went on mine, or vice versa.

        The opposite of that is that I left before you got back, and you left before I got back. Which is what Wien's condition checks.

          Write a Reply...