In-field auto calculate after submit

[JayDude]

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?

[Illusion]

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 ?

 

 

[fenway]

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.

[Illusion]

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.

[fenway]

You can never re-generated the start and end times with the difference alone.

[Illusion]

Ok but I was telling him that it is not so difficult to insert the difference along with start and stop time.

[JayDude]

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.

[Illusion]

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`

[JayDude]

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

[Illusion]

use sum function on difference by choosing plant_id as non-aggregate column.

[fenway]

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".