Skip to main content
C
College Kid
Inspiring
March 4, 2018
Question

Dynamically Calculating within a cfoutput

  • March 4, 2018
  • 1 reply
  • 800 views

I am using window 10, 64bit on a PC. I am also using Coldfusion 2016. I did a  Coldfusion query output that displays a history of reading input of water usage for an apartment from an electronic submeter. I am able to output the date of the meter reading and the gallons of water that was used. What I'm having difficulty with is creating two additional columns that dynamically calculate within the same query output the days lapse between each meter reading and the difference in the water used since the last read. Any insights. I've listed my code. 

<!---  query of submeter reading. --->

<cfquery name="list_water_usage" datasource="submeter">

select * from readsubmeter where reasubid = '#choose_submeterID.subid#' Order by reasubreaddate DESC</cfquery>

    This topic has been closed for replies.

    1 reply

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    March 4, 2018

    Depending on what database you use, you can probably do the math within the appropriate dialect of SQL for that database. You can then store the results of the operations within calculated columns.

    DATEDIFF (Transact-SQL) | Microsoft Docs

    GETDATE (Transact-SQL) | Microsoft Docs

    If you can't do it in ColdFusion, you can do it in ColdFusion after you get the results back. ColdFusion has lots of math and date functions just like your database.

    ColdFusion Help | Date and time functions

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    C
    College KidAuthor
    Inspiring
    March 4, 2018

    Yes sir,

    i am using MySql as the DBMS.

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 7, 2018

    <!---  query of submeter reading. --->

    <cfquery name="list_water_usage" datasource="submeter">

    select *

    from readsubmeter

    where reasubid = '#choose_submeterID.subid#'

    order by reasubreaddate DESC

    </cfquery>

    <!--- Create 2 arrays corresponding to the columns to be added --->

    <cfset timeDiffInHours = arrayNew(1)>

    <cfset usageDiffInGallons = arrayNew(1)>

    <!---

    My assumptions:

    1) The difference in time and usage corresponding to the first date are 0.

    2) The time difference is calculated in hours.

    3) The column name for water use is usage, and it is numeric.

    --->

    <!--- Populate the 2 arrays --->

    <cfset timeDiffInHours[1] = 0>

    <cfset usageDiffInGallons[1] = 0>

    <cfoutput query="list_water_usage">

        <cfif currentRow gt 1>

            <cfset timeDiffInHours[currentRow] = dateDiff("h", list_water_usage.reasubreaddate[currentRow-1], list_water_usage.reasubreaddate[currentRow])>

            <cfset usageDiffInGallons[1] = list_water_usage.usage[currentRow] - list_water_usage.usage[currentRow-1]>

        </cfif>

    </cfoutput>

    <!--- Add the 2 new columns to the query--->

    <cfset nCol1 = QueryAddColumn(list_water_usage, "timeDiffInHours", "integer", timeDiffInHours)>

    <cfset nCol2 = QueryAddColumn(list_water_usage, "usageDiffInGallons", "integer", usageDiffInGallons)>

    <!--- Dump the resulting query--->

    <cfdump var="#list_water_usage#" label="New list_water_usage query">

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices