I have 2 tables:
table 1:
|| *handtool_id* || *maintenance_interval_value* || *unit_unit_id* || *handtool_last_date_of_maintenance* || *handtool_next_date_of_maintenance* ||
|| 1 || 1 || 5 || 2014-11-07 || ||
|| 2 || 1 || 6 || 2014-11-07 || ||
|| 3 || 4 || 4 || 2014-11-07 || ||
table 2:
|| *unit_id* || *unit_name* || *unit_value* || *unit_parent_id* ||
|| 1 || Minute || 1 || 1 ||
|| 2 || Hour || 60 || 1 ||
|| 3 || Day || 1440 || 1 ||
|| 4 || Week || 10080 || 1 ||
|| 5 || Month || 32767 || 1 ||
|| 6 || Year || 525949 || 1 ||
What is the right syntax for calculating the *handtool_next_date_of_maintenance* from *maintenance_interval_value* and from *unit_unit_id* + *handtool_last_date_of_maintenance*? Thank you
As Mats Kindani suggested, you need to join the two tables to get the next maintenance date.
SELECT
table1.handtool_id,
table1.handtool_last_date_of_maintenance,
DATE_ADD(table1.handtool_last_date_of_maintenance,
INTERVAL table1.maintenance_interval_value*table2.unit_value SECOND) next_date_of_maintenance
FROM table1
JOIN table2 ON table1.unit_unit_id = table2.unit_id
The function I'm using to calculate the next maintenance date is called DATE_ADD. You can read up on its usage in previous link.
Finally, if you want to populate table1 with the "next date of maintenance", you'll have to transform my select query into an update query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments