[RESOLVED] Need help with simple query

h4x0rmx

I'm working on an older scheduler system whose database was poorly designed.
The system keeps the information by number of the week of the year and number of day of the week (pretty weird):

weekno *	weekday *	start *	end *	type *	user
20091		1		780	1050	8	99
20091		2		450	720	8	99
20091		4		480	720	8	99
20091		5		450	720	8	99
20092		1		780	1050	8	99
20092		2		450	720	8	99
[All fields are of type Int]

There is a function that returns the data in a more standard format:

Array
(
    [0] => Array
        (
            [id] => 22725
            [name] => Aaron G
            [date] => 2009-01-01
            [sched_in] => 8:00
            [sched_out] => 12:00
        )

and this function uses a query that looks like this:

SELECT * 
	FROM timetable 
	WHERE 
		user = '99' 
	AND 
		weekno BETWEEN '20091' AND '200913'

and of course it returns the data for weeks with numbers like 200841-200851 since these numbers are between 20091 AND 200913

I know how to work around the problem if I'm searching only for dates in a specific year:

SELECT * 
	FROM timetable 
	WHERE 
		user = '99' 
	AND 
		weekno BETWEEN '20091' AND '200913'
	AND
		CONVERT(weekno, CHAR) LIKE '2009%'

but what about when I need something in between 20084 and 200910?

any ideas?
Any help will be really appreciated.

sneakyimp

You could generate a big list of acceptable weekno values and use the SQL "IN" statement

SELECT * 
	FROM timetable 
	WHERE 
		user = '99' 
	AND 
		weekno IN ('20084', '20085', '20086', '20087')
	AND
		CONVERT(weekno, CHAR) LIKE '2009&#37;'

That's pretty easy in PHP

$start = '20084';
$end = '200910';
$start_year = substr($start, 0, 4);
$start_week = substr($start, 4);
$end_year = substr($end, 0, 4);
$end_week = substr($end, 4);

$valid_weeknos = array();
for($i=$start_year, $i<=$end_year; $i++) {
  if ($i == $start_year) {
    $initial_week = $start_week;
  } else {
    $initial_week = 1;
  }
  if ($i == $end_year) {
    $final_week = $end_week;
  } else {
    $final_week = 52; // should this be 51?  52?  53?  We might need to do some extra work
  }
  for($j=$initial_week; $j <= $final_week; $j++) {
    $valid_weeknos[] = "'" . $i . $j . "'";
  }
}

if (sizeof($valid_weeknos) > 0) {
  $sql = "SELECT * 
	FROM timetable 
	WHERE 
		user = '99' 
	AND 
		weekno IN (" . implode(',', $valid_weeknos) . ")";

} else {
  die('no valid weeknos');
}

That'll make for a long query but sql can generally handle these things.

devinemke

this is a total kludge but i think it should work

SELECT * FROM timetable WHERE CONCAT(SUBSTR(weekno, 1, 4), SUBSTR(SUBSTR(weekno FROM 5) / 100 FROM 2)) BETWEEN 2008.04 AND 2009.10 ORDER BY weekno

the trick is you need to use 2008.04 and 2009.10 is your WHERE clause rather than 20084 AND 200910

h4x0rmx

thanks guys!
I appreciate your help!