JayDude Posted May 18, 2012 Share Posted May 18, 2012 I have a table for capturing machine hours. This includes start and stop hours. I am wondering if it is possible to add a third field to the table for "total", which auto-calculates the difference between the stop and start fields, after the first two fields have been entered? I know this can be done from a standard query, but if I can have a field already calculating each entry as I enter to go to the next line / entry, my queries will become substancially smaller and less difficult. ie (stop - start) = total {where total is a hidden field from the user and can not be entered manually} date machine_id start stop total 12/06/21 PCD123 2595.0 2601.3 6.3 query or formula should be within the table structure, is this possible? Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/ Share on other sites More sharing options...
Illusion Posted May 18, 2012 Share Posted May 18, 2012 Absolutely no need to store the difference as you can calculate the same while fetching the data . As per your question ...yes it is possible through triggers to update the third field value whenever a row is inserted . if at all you still want to store the difference .. why it is difficult to insert the difference along the start and stop times for every new record ? Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1346603 Share on other sites More sharing options...
fenway Posted May 19, 2012 Share Posted May 19, 2012 if at all you still want to store the difference .. why it is difficult to insert the difference along the start and stop times for every new record ? Never a good idea to throw out raw data. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1346848 Share on other sites More sharing options...
Illusion Posted May 19, 2012 Share Posted May 19, 2012 if at all you still want to store the difference .. why it is difficult to insert the difference along the start and stop times for every new record ? Never a good idea to throw out raw data. I didn't understand it. Enlighten me ...please. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1346854 Share on other sites More sharing options...
fenway Posted May 20, 2012 Share Posted May 20, 2012 You can never re-generated the start and end times with the difference alone. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347030 Share on other sites More sharing options...
Illusion Posted May 20, 2012 Share Posted May 20, 2012 Ok but I was telling him that it is not so difficult to insert the difference along with start and stop time. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347044 Share on other sites More sharing options...
JayDude Posted May 21, 2012 Author Share Posted May 21, 2012 Ok here is the query so far, and it works fine for one part of the report. Now I also need to "somehow" get the total accumalted hours from this query and this is where I get stuck, maybe there is a way within the query to get the result, then a trigger would not be required: SELECT`plant_id` , MIN(`plant_hrs_start`) min_hrs, MAX(`plant_hrs_stop`) max_hrs, (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) )total, `location_id` FROM`plant_hrs` WHERE MONTH(`plant_hrs_date`)= MONTH( CURRENT_DATE ) GROUP BY`plant_id` Outcome: plant_id min_hrs max_hrs total location_id ADT01 16909.0 16929.0 20.0 Middelkraal ADT07 28454.0 28510.0 56.0 Middelkraal DRZ14 4019.0 4120.0 101.0 Middelkraal EX06 5566.0 5816.0 250.0 Middelkraal KD01 12774.0 12956.0 182.0 Middelkraal KD03 16316.0 16335.0 19.0 Middelkraal KD04 14273.0 14455.0 182.0 Middelkraal KD06 16416.0 16495.0 79.0 Middelkraal KD07 1566.0 1597.0 31.0 Middelkraal As you can see the first part of my query works fine, but what if I need to state the TOTAL for only the "KD..."s which is 493 Hrs. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347209 Share on other sites More sharing options...
Illusion Posted May 21, 2012 Share Posted May 21, 2012 set accumulated_hours=0; SELECT`plant_id` , MIN(`plant_hrs_start`) min_hrs, MAX(`plant_hrs_stop`) max_hrs, (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) )total,@accumulated_hours:=@accumulated_hours+ (MAX(`plant_hrs_stop`)- MIN(`plant_hrs_start`) ) as accumulation , `location_id` FROM`plant_hrs` WHERE MONTH(`plant_hrs_date`)= MONTH( CURRENT_DATE ) and plant_id LIKE 'KD%' GROUP BY`plant_id` Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347216 Share on other sites More sharing options...
JayDude Posted May 21, 2012 Author Share Posted May 21, 2012 Thanx, but I still see each individual plant's total, what I need is to see only one line with the "KD..."s total hours combined ie the required output should be like the following: Total 493 Hrs Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347220 Share on other sites More sharing options...
Illusion Posted May 21, 2012 Share Posted May 21, 2012 use sum function on difference by choosing plant_id as non-aggregate column. Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347227 Share on other sites More sharing options...
fenway Posted May 21, 2012 Share Posted May 21, 2012 Ok but I was telling him that it is not so difficult to insert the difference along with start and stop time. Sorry -- I read "instead" not "along". Quote Link to comment https://forums.phpfreaks.com/topic/262734-in-field-auto-calculate-after-submit/#findComment-1347436 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.