Skip to main content
J
Jatrix
Inspiring
December 9, 2008
Answered

Performing Calculations using Record Count

  • December 9, 2008
  • 4 replies
  • 721 views
I am working on a form that calculates an employees vacation time. THe employee starts with a static balance, say 120. The employee can then enter their vacation time as they take it. They can only enter it in increments of either 8 hours or 4 hours, so each record in the database will reflect either 8 or 4 hours. I want to be able to display a current balance for the employee. So if the employee logs in and takes a week off, they will have to enter 5 records with 8 hours each. How do I calculate this to get the desired current balance of 80? I am not very fluent with calculations in CF, but think it must involve the record set. THis is probably very easy, but I am stumped.

Any help would be great! Also if I didnt explain this correctly, let me know and I will try to offer more details.
    This topic has been closed for replies.
    Correct answer Newsgroup_User
    Jatrix wrote:
    >
    > Any help would be great! Also if I didnt explain this correctly, let me know
    > and I will try to offer more details.
    >

    Well, you *could* just add up all the 'vactaionTime' fields of each
    record in the record set. I.E.

    <cfset totalVac = 0>
    <cfloop query="empVacQry">
    <cfset totalVac = totalVac + empVacQry.vacTime>
    </cfloop>
    <cfoutput>#totalVac#</cfoutput>

    But this really is much easier to do with some simple SQL.

    SELECT empID, sum(vacTime) AS totalVacTime
    FROM EmployeeTable
    GROUP BY empID

    <cfoutput query="empVacQry">
    #empID# #totalVacTime#<br>
    </cfoutput>

    4 replies

    V
    vietnamteenager
    Participant
    December 10, 2008
    so explain again plz, i did that and get the error
    J
    JatrixAuthor
    Inspiring
    December 9, 2008
    THATS IT!!!!!!! OH THANK YOU THANK YOU!!!!

    I love this forum.

    This is a big project I am working on, so you all may see me back.

    THANKS AGAIN IAN!!!!!!
    N
    Newsgroup_UserCorrect answer
    Inspiring
    December 9, 2008
    Jatrix wrote:
    >
    > Any help would be great! Also if I didnt explain this correctly, let me know
    > and I will try to offer more details.
    >

    Well, you *could* just add up all the 'vactaionTime' fields of each
    record in the record set. I.E.

    <cfset totalVac = 0>
    <cfloop query="empVacQry">
    <cfset totalVac = totalVac + empVacQry.vacTime>
    </cfloop>
    <cfoutput>#totalVac#</cfoutput>

    But this really is much easier to do with some simple SQL.

    SELECT empID, sum(vacTime) AS totalVacTime
    FROM EmployeeTable
    GROUP BY empID

    <cfoutput query="empVacQry">
    #empID# #totalVacTime#<br>
    </cfoutput>
    D
    Dan_Bracuk
    Inspiring
    December 9, 2008
    depending on what is being stored, you can do something like

    select static_balance - sum(hours_taken)
    from thetables
    where the conditions are met
    group by static_balance
    J
    JatrixAuthor
    Inspiring
    December 9, 2008
    Would this be set up as a CFSET?

    <CFSET Current = static_balance - sum(hours_taken)>

    Then I can just display Current?

    I dont think I could do it in a query as the two fields are stored in different DB's and have different datasources. I wouldnt know how to set that up.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices