Thomas (and list), The solution, as previously posted, is flawed/incomplete - mea culpa. Excuse: I was interrupted three times from typing the word SELECT until pressing Send, and then rushing to get on to the next call on my time...
> WHERE bstart < afinish > OR bfinish > astart; Check back with the diagram and you will see that a bstart clause needs to also check that bstart >= astart to trigger rejection - if the leave starts AND finishes before the 'replacement's' leave, then all is well - similarly for second clause. Now, if you prefer to inform a positive response, eg "leave may be granted", then when you turn things around, pay special attention to the relationship and changing ORs and ANDs. Better if we change the whole logic around and say "when can we grant annual leave?" because then the answer becomes: if bfinish < astart or fstart > afinish. This has the additional advantage of 'scaling', if the employee has more than one potential 'replacement'! In all approaches beware the special case: that the 'replacement' has no outstanding request for leave! Again apologies, =dn > > SELECT * from $DB_TBLName WHERE > > (Trim(WorkerEmail)='$userReplacementEmail') > > AND AND OR OR OR OR OR > > This is all a bit complicated, and a simple boy like me gets lost too > easily. KISS principle: Keep it simple... > > You have two employees: > A, so named because he Already has Annual leave Approved, and > B, so named because he is Begging to Be allowed to Break out. > > Let's imagine we're playing with a wall-chart/wall-planner to assess > requests prior to granting approval. So we mark off which days A will be > away and then view which days B would like to go against existing requests. > > Month A Month B Month C Month etc > A <-------------------> > B <........................> > > Assuming the email/fonts haven't mucked-up my sketch, you can see that B's > request should be denied, because A has already booked the time-out. > > Now let's use the diagram to build an algorithm. How many ways are there > that A and B could overlap? (hint: four) Not overlap? (hint: two) Which is > the easiest to implement in code? > > Now you can ask, which language should I code in? Answer: stay as close to > the source as possible, ie use SQL (or in this case pseudo-SQL(!)). > > SELECT a AS Colleague, CONCAT( astart - afinish ) AS PermissionDenied > FROM tbl > WHERE bstart < afinish > OR bfinish > astart; > > Before this will work, you will need to add another WHERE clause to > establish who is the employee's "replacement". The above lists a reason for > rejection - you can reverse the logic, at your peril. > > Trust this assists, > =dn > > PS The complicating question I wanted to ask from the word "go" is: what if > one person can have more than one other employee act as his/her > "replacement"? > > > > > > 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 > > > > > > ================ > > > > ...so 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 > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
