Highlighted

Dynamically Calculating within a cfoutput

Explorer ,
Mar 04, 2018

Copy link to clipboard

Copied

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>

Views

238

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Dynamically Calculating within a cfoutput

Explorer ,
Mar 04, 2018

Copy link to clipboard

Copied

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>

Views

239

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 04, 2018 0
Adobe Community Professional ,
Mar 04, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 04, 2018 0
Explorer ,
Mar 04, 2018

Copy link to clipboard

Copied

Yes sir,

i am using MySql as the DBMS.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 04, 2018 0
Adobe Community Professional ,
Mar 07, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 07, 2018 1
LEGEND ,
Mar 07, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 07, 2018 0
Adobe Community Professional ,
Mar 07, 2018

Copy link to clipboard

Copied

Thanks, WolfShade​. How kind!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 07, 2018 0
BKBK LATEST
Adobe Community Professional ,
Mar 07, 2018

Copy link to clipboard

Copied

<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]>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 07, 2018 0