Trying to keep a running total

[sue0912]

Hi everyone,

I have a little job application where I am trying to track employee's hours on a particular job. When the employee puts in their hours worked, I would like to keep a running total because it may be days worth of hours before the job is closed....anywhere I wrote the following code:

<cfquery name="gethrs" datasource="Intranet">
Select woid, hours_worked
From acsjob
where woid = #form.woid#
</cfquery>
<CFSET oldhours="#Gethrs.hours_worked#">
<CFSET newhours="#form.hours_worked#">
<CFTRY>
<CFSET Hours="#NumberFormat(oldhours + newhours, "9999.99")#">
<CFCATCH>
<CFSET Hours="0">
</CFCATCH>
</CFTRY>

What happens is that if someone is in the job to update data other then the hours worked, the code I wrote takes the hours worked and adds them together...so if 30 hours was in the field then it updates it to 60..

Not sure what I am doing wrong. any suggestions would be great. Thanks Sue

 

[cfSearching]

What happens is that if someone is in the job to update data other then the hours worked, the code I wrote takes the hours worked and adds them together...so if 30 hours was in the field then it updates it to 60..
<CFSET Hours="#NumberFormat(oldhours + newhours, "9999.99")#">

It looks like the code always adds the old and new hours together, even if they were changed.

Instead of trying to keep a store a running total, why not just run a SUM on the table containing their daily/weekly hours when needed.

SELECT SUM(Hours) AS TotalHoursWorked
FROM EmployeeHoursTable
WHERE JobNumber = 'jobabc'



----------------------------------

http://cfsearching.blogspot.com/

 

[sue0912]

You are right. I have been thinking about this since I posted it.

It will never calculate correctly as I have it adding every time.

Not sure how I am going to fix this. I guess the best way to describe this little application is that it is similar to a WorkOrder. There are several job(s) to each work order and then the employees track their hours worked for each work order.

I have the system setup so at anytime they can go in and update their hours worked. But the way I have it coded on the updated page, adds it every single time.

 

[cfSearching]

So are you storing employee hours on a daily (weekly, etcetera) basis or just total employee hours for each job?

If you are tracking detailed hours, then it is better to calculate the total dynamically with sql, as needed.

If you are just storing the total, you could do a conditional update. ie If the employee entered new hours, update the hours_worked value.

However, a lot depends on what totals you are storing and whether the information may be updated concurrently by multiple employees.





----------------------------------

http://cfsearching.blogspot.com/