StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCalculating time length from a newly created record and then inserting that time into a column in the record?
text
Body
copied!<p>I have a database like so:</p> <p><a href="http://i.imgur.com/0vzoT.png" rel="nofollow">http://i.imgur.com/0vzoT.png</a></p> <p>The main table called </p> <p>"Procedure" can have many rows in the "Procedure_Event" table. Each procedure event row has a column called "time".</p> <p>This is here so I can be able to calculate the total time from the first row to the last row in that record.</p> <p>This is what happens:</p> <blockquote> <p>-Row in Procedure table created and data is added. -Works</p> <p>-Procedure event rows are created in "Procedure Event" table and are filled. -Works</p> <p>-Calculation of time length using the first row and the last row in that record - Not working -Inserting calculated length of time back into the Procedure row column which was created at the start. This column will be called "procedure_length"</p> </blockquote> <p>Why am I doing this? This will allow users to find procedures </p> <p>This is my query:</p> <pre><code>SELECT procedure_id, SUBTIME(events_max.start, events_min.start) AS duration FROM procedure JOIN ( SELECT procedure_id, SELECT MAX(time) AS start FROM procedure_events GROUP BY procedure_id ) AS events_max USING ( procedure_id ) JOIN ( SELECT procedure_id, SELECT MIN(time) AS start FROM procedure_events GROUP BY procedure_id ) AS events_min USING ( procedure_id ) GROUP BY procedure_id </code></pre> <p>Should I put this in a stored procedure?</p> <p>Currently my query is getting error 1064:</p> <p>Thanks for your time.</p>

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