Dynamically Calculating within a cfoutput

[College_Kid]

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>

[Dave Watts]

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

[College_Kid]

Yes sir,

i am using MySql as the DBMS.

[BKBK]

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

[WolfShade]

BKBK,

I had been pondering the OP request, yesterday, trying to think of a formula.. then work intervened.    The audacity. 

I hadn't even considered arrays, and I use them frequently.  Nice bit o' code, there.

V/r,

^ _ ^

[BKBK]

Thanks, WolfShade​. How kind!

[BKBK]

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

Thanks again, @WolfShade. There was indeed a typo. That line should read:

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