doing math operation in database

[sulistyanto]

i want to make a present of list for employees in my officce with barcode.The problem is how i can get data from database & doing mathematical operation & put it back the result of that mathematical operation inthe same database,sametable,&same row.
CODE TIME-IN TIME-OUT WORK-DURATION
1741 07.30 16.00

WORK-DURATION=(TIME-OUT)-(TIME-IN)
can somebody help me?

 

[cjard]

Decide where you want to do this sum: On the database server, or on the client?

You answer properly we need to know more about how you hold this info.

I'd do it on the database, when a user clocks out, they type their ID, and you do:

UPDATE workingHours SET endTime = <now>, workedHours = <now> - startTime WHERE employeeID = <whatever> and endTime is null


Only records with a blank endTime will be affected, which should only ever be one!

Note you didnt say what database you are using so i couldnt target the advice to vendor specific functions for getting the current time

Note2: this should really be in the database forum, but which database, it is not clear

 

[sulistyanto]

doing math in database

Decide where you want to do this sum: On the database server, or on the client?

You answer properly we need to know more about how you hold this info.

I'd do it on the database, when a user clocks out, they type their ID, and you do:

UPDATE workingHours SET endTime = <now>, workedHours = <now> - startTime WHERE employeeID = <whatever> and endTime is null


Only records with a blank endTime will be affected, which should only ever be one!

Note you didnt say what database you are using so i couldnt target the advice to vendor specific functions for getting the current time

Note2: this should really be in the database forum, but which database, it is not clear

------------------------------------------------------------------------
thanx with your advice,cjard.
But there is more different schedule every day,such as:
monday-thursday,saturday & sunday break at: 12:00,18:00,24:00
friday break at: 11:45,18:00,24:00
but sometime that break time must can be change by input breaktime & insert it in database.It's impossible doing operation in database so i'm doing operation in source code.I use MS Access database.Do you have any suggestion?

 

[cjard]

When I was writing my time-plotting database i didnt particularly care when the breaks were; I only cared how long they were and deducted the maximum. If a person gets 1.25 hours per day for lunch, unpaid, then reduce the number of hours they worked that day to (endTime - startTime) -1.25 hours

Who cares when the breaks were taken?