StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>ok, given that you want the data across regular 2 hourly intervals, you could try something like this:</p> <pre><code>SELECT FLOOR(hour(S.submit_date)/2)*2, U.user_name,ROUND(SUM((TA.task_average*TC.completed)/60),2) AS equiv1, S.submit_date SUM(TC.completed) AS ttasks1, FROM `summary` S JOIN users U ON U.user_id = S.user_id JOIN tasks TA ON TA.task_id = S.task_id JOIN tcompleted TC ON TC.tcompleted_id = S.tcompleted_id JOIN minutes M ON M.minutes_id = S.minutes_id WHERE DATE(submit_date) = curdate( ) GROUP BY U.user_name, FLOOR(hour(S.submit_date)/2) LIMIT 0 , 30 </code></pre> <p>where <code>FLOOR(hour(S.submit_date)/2)*2</code> will map each hour to the first (even) hour of every 2 and you can group by this value. ie.</p> <pre><code>0, 1 -> 0 2, 3 -> 2 4, 5 -> 4 etc... </code></pre> <hr> <h2>update with php included:</h2> <p>some notes:</p> <ul> <li>i've used mysqli</li> <li>i've left joined the original query with an <code>hours</code> derived table to ensure there are no 'gaps' in the time intervals (assumed 6:00 - 20:00)</li> <li>i've ordered by <code>user</code>, so we as we loop through the results we can print table cells for a given user and then print a new table row when the user changes.</li> </ul> <p>here's the code:</p> <pre><code>echo '<table border=1><tr><td></td>'; for ($i=6; $i<=18; $i=$i+2) { echo '<td colspan=2>'.$i.' - '.($i+2).'</td>'; } $mysqli = new mysqli('MY_HOST', 'MY_USER', 'MY_PASSWORD', 'MY_DATABASE'); $sql = " SELECT user_name, IFNULL(equiv1,0) AS equiv1, IFNULL(ttasks1,0) AS ttasks1, hour FROM ( SELECT 6 AS hour UNION SELECT 8 UNION SELECT 10 UNION SELECT 12 UNION SELECT 14 UNION SELECT 16 UNION SELECT 18 ) hours LEFT JOIN ( SELECT FLOOR(hour(S.submit_date)/2)*2 as task_hour, U.user_name, ROUND(SUM((TA.task_average*TC.completed)/60),2) AS equiv1, S.submit_date SUM(TC.completed) AS ttasks1 FROM `summary` S JOIN users U ON U.user_id = S.user_id JOIN tasks TA ON TA.task_id = S.task_id JOIN tcompleted TC ON TC.tcompleted_id = S.tcompleted_id JOIN minutes M ON M.minutes_id = S.minutes_id WHERE DATE(submit_date) = curdate( ) GROUP BY U.user_name, FLOOR(hour(S.submit_date)/2) LIMIT 0 , 30 ) task_summary ON hours.hour = task_summary.task_hour ORDER BY user_name, hour "; $result = $mysqli->query($sql); $user_name = ''; while ($row = $result->fetch_assoc()) { if ($user_name <> $row['user_name']){ echo '</tr><tr><td>'.$row['user_name'].'</td>'; //start a new row if user changes $user_name = $row['user_name']; //update user variable for checking on next iteration } echo '<td>'.$row['equiv1'].'</td>'; echo '<td>'.$row['ttasks1'].'</td>'; } echo '</tr><table>'; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload