Adding relative week number column to MySQl results

Question

I have a table with 3 columns: user, value, and date. The main query returns the values for a specific user based on a date range:

SELECT date, value FROM values
WHERE user = '$user' AND
date BETWEEN $start AND $end

What I would like is for the results to also have a column indicating the week number relative to the date range. So if the date range is 1/1/2010 - 1/20/2010, then any results from the first Sun - Sat of that range are week 1, the next Sun - Sat are week 2, etc. If the date range starts on a Saturday, then only results from that one day would be week 1. If the date range starts on Thursday but the first result is on the following Monday, it would be week 2, and there are no week 1 results.

Is this something fairly simple to add to the query? The only ideas I can come up with would be based on the week number for the year or the week number based on the results themselves (where in that second example above, the first result always gets week 1).

Example:

 $start = "05/27/2010";
 $end =   "06/13/2010";

 //Query 

 Result:

 week      date            user       value
  1        05/28/2010      joe        123
  3        06/07/2010      joe        123
  3        06/08/2010      joe        123
  4        06/13/2010      joe        123

Answer 1

This can easily be done with this simple and obvious expression :)

SELECT ...,
    FLOOR(
        (
            DATEDIFF(date, $start) +
            WEEKDAY($start + INTERVAL 1 DAY)
        ) / 7
    ) + 1 AS week_number;

Some explanation: this expression just calculates difference between the given date and the start date in days, then converts this number to weeks via FLOOR("difference in days" / 7) + 1. That's simple, but since this works only when $start is Sunday, we should add an offset for other week days: WEEKDAY($start + INTERVAL 1 DAY) which equals to 0 for Sun, 1 for Mon, ..., 6 for Sat.