need some help regarding query

[greedyisg00d]

Sorry guys my topic title seems irrelevant because I cannot think of any other title for it. Anyways I am currently working on some sort of a scheduler which all data are stored into a database. My problem is my manager wants a function that goes like this:

Employee A - has a business trip for Week 1 of July
Employee B - has a business trip for Week 3 of July
Employee C - has a business trip for Week 4 of July

Thus my manager wants to know which date will all the employees will be available which in this case it will be in the 2nd week of July. Sample codes is much appreciated so that I can work or play around with it.

Thanks in advance

[johanafm]

Since I've no idea what your schema looks like, I'll make things up as I go along. Also, this was written on the fly and is untested, which means it most likely won't pass the parser.. But it should give you the idea anyway.

CREATE TABLE away (
	eid	SMALLINT UNSIGNED NOT NULL,
	start	INT UNSIGNED NOT NULL,
	end	INT UNSIGNED NOT NULL,
	PRIMARY KEY (eid, start)
);

$julyStart = mktime(0, 0, 0, 7, 1, 2009);
$julyEnd = mktime(0, 0 , 0, 8, 1, 2009);
$oneWeek = 3600 * 24 * 7;
$sql = "SELECT start, end FROM away WHERE start >= $julyStart AND end < $julyEnd";

// If you're only interested in some employees in a given query:
$sql = "SELECT start, end FROM away WHERE start >= $julyStart AND end < $julyEnd AND eid IN (1, 4, 5, 6, 10)";


$result = mysql_query($sql);
$away = array();
while ($row = mysql_fetch_assoc($result)) {
	$away[] = $row;

/* If you have a lot of data, this is where you'd want to create an array of
	indexes that points to the start of each week in $away so that you don't
	have to go over the whole array each time. */
}

$weekStart = $julyStart;
$weekEnd = $julyStart + $oneWeek;

$weeks = array();
$weekIndex = 0;
while($weekStart < $julyEnd) {
	$weeks[$weekIndex] = true;	// Assume everyone is available
	for ($i = 0; $i < count($away) && $weeks[$weekIndex] == true; ++$i) {
		// Found an employee who is away
		if ($away[$i]['start'] < $weekEnd && $weekStart < $away[$i]['end']) {
			$weeks[$weekIndex] = false;
		}
	}
	++$weekIndex;
	$weekStart = $weekEnd;
	$weekEnd += $oneWeek;
}

for (var $i = 0; $i < count($weeks); ++$i) {
	if ($weeks[$i])
		echo "Week " . ($i + 1) . " works";
}