Q:

Compare dates same column mySQL Laravel

I have a 'tasks' table that looks like the following:

I need to calculate the start date offset for each record; I need to calculate how many days after the min/lowest start date the start date for each record is.
I am doing this in Laravel 4.  The table is mySQL.  I would really prefer a Laravel query, but I can probably also include raw mySQL.
My query (it selects some other things, too) doesn't work 
    $tasks = DB::table('tasks')->select(array(DB::raw("TIMESTAMPDIFF(DAY, MIN(start), start) AS offset"), DB::raw("TIMESTAMPDIFF(DAY, start, end) AS length"), 'user_id', 'name', 'desc','start','end','finished'))->where('temp_id', $id)->orderBy('start')->get();

"MIN(start)" for the self compare seems to be the problem.  How do I calculate how many days after the min start date the start date for each record is?

A:

I see two possible solutions to solve the incorrect usage of MIN(start) on a non-aggregated result set. 
Option one: Save the variable for the minimum date and use that in your query:
$min_date = DB::table("tasks")->selectRaw("id, MIN(start) AS min")->groupBy("id")->first();
echo $min_date->min; // 2015-08-15 00:00:00, assuming my syntax was correct.

$tasks = DB::table('tasks')
->select(array(DB::raw("TIMESTAMPDIFF(DAY, ".$min_date->min.", start) AS offset"), DB::raw("TIMESTAMPDIFF(DAY, start, end) AS length"), 'user_id', 'name', 'desc','start','end','finished'))
->where('temp_id', $id)
->orderBy('start')
->get();

Option two: Use a subquery in the select:
$tasks = DB::table('tasks')
->select(array(DB::raw("TIMESTAMPDIFF(DAY, (SELECT MIN(start) FROM tasks), start) AS offset"), DB::raw("TIMESTAMPDIFF(DAY, start, end) AS length"), 'user_id', 'name', 'desc','start','end','finished'))
->where('temp_id', $id)
->orderBy('start')
->get();

Note on MIN() usage: It can be used without aggregating results, but only returns a single row, unless used in a subquery. 
Examples: 
SELECT start FROM tasks; /*6 Rows*/
SELECT MIN(start) FROM tasks; /*1 Row*/
SELECT MIN(start), start FROM tasks; /*1 Row*/
SELECT (SELECT (MIN(start) FROM tasks) AS min_start, start FROM tasks; /*6 Rows*/

The last example will return the results from each row alongside the minimum start found in tasks. Hope this helped clear your issue up. Either option 1 or option 2 should help, but leave a comment in you need help with anything else.

