mysql - How to calculate average time? -
i trying figure out how take average of time buildof items.
this equation have used gather time:
($f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()) / (24* 60 * 60 * 1000) + " days " + (($f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()) / (60 * 60 * 1000)) % 24 + " hour(s), " + (($f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()) / (60 * 1000)) % 60 + " minute(s)" this photos of disign , variables, along xml, explain situation.
question: how fix avgtobuild variable averages of calculated time build?
<?xml version="1.0" encoding="utf-8"?> <jasperreport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="bom build time" pagewidth="595" pageheight="840" columnwidth="555" leftmargin="20" rightmargin="20" topmargin="20" bottommargin="20" uuid="775a7e35-9af8-4206-a155-b05a478c35b0"> <property name="ireport.zoom" value="1.5"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <parameter name="bomnumber" class="java.lang.string"> <parameterdescription><![cdata[bom number:]]></parameterdescription> </parameter> <parameter name="to" class="java.util.date"/> <parameter name="from" class="java.util.date"/> <querystring> <![cdata[select datediff(day,mo."datecreated",mo."datecompleted") daytobuild, company."name" company_name, bom."num" bom_num, mo."datecreated" mo_datecreated, mo."num" mo_num, mo."datecompleted" mo_datecompleted, moitem."bomid" moitem_bomid, moitem."moid" moitem_moid, moitem."qtytofulfill" moitem_qtytofulfill, bomitemtype."id" bomitemtype_id, wo."num" wo_num "bom" bom inner join "moitem" moitem on bom."id" = moitem."bomid" inner join "mo" mo on moitem."moid" = mo."id" inner join "bomitemtype" bomitemtype on moitem."typeid" = bomitemtype."id" inner join "wo" wo on moitem."id" = wo."moitemid", "company" company bom."num"=$p{bomnumber} , mo."datecompleted" between $p{from} , $p{to} order 3 asc]]> </querystring> <field name="daytobuild" class="java.lang.long"/> <field name="company_name" class="java.lang.string"/> <field name="bom_num" class="java.lang.string"/> <field name="mo_datecreated" class="java.sql.timestamp"/> <field name="mo_num" class="java.lang.string"/> <field name="mo_datecompleted" class="java.sql.timestamp"/> <field name="moitem_bomid" class="java.lang.integer"/> <field name="moitem_moid" class="java.lang.integer"/> <field name="moitem_qtytofulfill" class="java.lang.double"/> <field name="bomitemtype_id" class="java.lang.integer"/> <field name="wo_num" class="java.lang.string"/> <variable name="timetobuild" class="java.lang.string"> <variableexpression><![cdata[($f{mo_datecompleted}.gettime()- $f{mo_datecreated}.gettime()) / (24* 60 * 60 * 1000) + " days " + (($f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()) / (60 * 60 * 1000)) % 24 + " hour(s), " + (($f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()) / (60 * 1000)) % 60 + " minute(s)"]]></variableexpression> </variable> <variable name="avgtimetobuild" class="java.lang.string" calculation="average"> <variableexpression><![cdata[$v{timetobuild}]]></variableexpression> </variable> <title> <band height="27" splittype="stretch"> <textfield> <reportelement x="0" y="0" width="555" height="25" uuid="40060ef5-07fd-4875-9f81-10b65d60d11d"/> <textelement textalignment="center" verticalalignment="top"> <font size="16" isbold="true"/> </textelement> <textfieldexpression><![cdata[$f{company_name}]]></textfieldexpression> </textfield> </band> </title> <pageheader> <band height="50"> <statictext> <reportelement x="0" y="0" width="555" height="25" uuid="b17515a1-2396-49f1-ad96-452ce08d720e"/> <textelement textalignment="center"> <font size="14"/> </textelement> <text><![cdata[bom build time report]]></text> </statictext> <statictext> <reportelement x="0" y="25" width="190" height="20" uuid="24836422-ce77-43c9-8268-a8f13d2d11b5"/> <textelement textalignment="right"> <font size="14"/> </textelement> <text><![cdata[date range:]]></text> </statictext> <textfield> <reportelement x="190" y="25" width="100" height="20" uuid="e25d36f8-e42b-4015-bc41-7bfbc9040119"/> <textelement textalignment="right"> <font size="14"/> </textelement> <textfieldexpression><![cdata[$p{from}]]></textfieldexpression> </textfield> <statictext> <reportelement x="290" y="25" width="20" height="20" uuid="45821608-63dc-459f-b2c3-41a6156b72dd"/> <textelement textalignment="center"> <font size="14"/> </textelement> <text><![cdata[-]]></text> </statictext> <textfield> <reportelement x="310" y="25" width="100" height="20" uuid="05fd3fd8-735d-402c-9d98-bdd3f9f52378"/> <textelement> <font size="14"/> </textelement> <textfieldexpression><![cdata[$p{to}]]></textfieldexpression> </textfield> </band> </pageheader> <columnheader> <band height="24" splittype="stretch"> <statictext> <reportelement x="0" y="0" width="75" height="20" uuid="b59f6065-7fc9-482d-9e08-e381ac697304"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[bom #]]></text> </statictext> <statictext> <reportelement x="80" y="0" width="100" height="20" uuid="4ec40990-bdfe-415d-a7ae-b50e315d00ef"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[start date]]></text> </statictext> <statictext> <reportelement x="185" y="0" width="100" height="20" uuid="032d2ae9-99fe-4c6f-8cf4-24b9c5367a44"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[finish date]]></text> </statictext> <statictext> <reportelement x="415" y="0" width="140" height="20" uuid="55b731d5-b826-4ee1-b7d2-4a83cabd4ef8"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[time build]]></text> </statictext> <statictext> <reportelement x="290" y="0" width="50" height="20" uuid="807ee552-ea26-4891-bc72-a433aeab1866"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[qty]]></text> </statictext> <statictext> <reportelement x="345" y="0" width="50" height="20" uuid="cb39685d-f7c8-432f-9b69-454ff5fe2134"/> <textelement> <font size="12" isbold="true"/> </textelement> <text><![cdata[wo #]]></text> </statictext> <line> <reportelement x="0" y="19" width="555" height="1" uuid="266ffac8-70a0-4756-9191-5de40f532d9f"/> </line> </band> </columnheader> <detail> <band height="24" splittype="stretch"> <textfield isblankwhennull="true"> <reportelement x="0" y="0" width="75" height="20" isremovelinewhenblank="true" uuid="3b256693-f18d-4ef9-89ec-7890481d1855"/> <textfieldexpression><![cdata[$f{bom_num}]]></textfieldexpression> </textfield> <textfield isblankwhennull="true"> <reportelement x="80" y="0" width="100" height="20" isremovelinewhenblank="true" uuid="7c01356e-4c95-4e15-9691-632e0b84946e"/> <textfieldexpression><![cdata[$f{mo_datecreated}]]></textfieldexpression> </textfield> <textfield isblankwhennull="true"> <reportelement x="185" y="0" width="100" height="20" isremovelinewhenblank="true" uuid="516bc38e-99f3-486f-ae74-c8cfe6a5b5b1"/> <textfieldexpression><![cdata[$f{mo_datecompleted}]]></textfieldexpression> </textfield> <textfield isblankwhennull="true"> <reportelement x="415" y="0" width="140" height="20" isremovelinewhenblank="true" uuid="aa538d4a-48ec-4b72-84ed-e9e889dcaee5"/> <textelement> <paragraph tabstopwidth="40"/> </textelement> <textfieldexpression><![cdata[$v{timetobuild}]]></textfieldexpression> </textfield> <textfield isblankwhennull="true"> <reportelement x="290" y="0" width="50" height="20" isremovelinewhenblank="true" uuid="cf252514-6297-405d-a12e-6424332e6d10"/> <textfieldexpression><![cdata[$f{moitem_qtytofulfill}]]></textfieldexpression> </textfield> <textfield> <reportelement x="345" y="0" width="50" height="20" isremovelinewhenblank="true" uuid="6637a124-0145-4e56-b3eb-7af3bae5446e"/> <textfieldexpression><![cdata[$f{wo_num}]]></textfieldexpression> </textfield> </band> </detail> <summary> <band height="22"> <statictext> <reportelement x="0" y="0" width="150" height="20" uuid="5ac0e1eb-dc7b-4866-88aa-3c1a9c3ef4a9"/> <textelement> <font size="12"/> </textelement> <text><![cdata[average time build:]]></text> </statictext> <textfield> <reportelement x="150" y="0" width="150" height="20" uuid="860dd902-9791-443e-9a9d-3dedab365b23"/> <textfieldexpression><![cdata[$v{avgtimetobuild}]]></textfieldexpression> </textfield> </band> </summary>
you need calculate average of numeric value not string value.
lets calculate average in milliseconds class="java.lang.long"
<variable name="avgtimetobuild" class="java.lang.long" calculation="average"> <variableexpression><![cdata[$f{mo_datecompleted}.gettime()-$f{mo_datecreated}.gettime()]]></variableexpression> </variable> to display apply same expression in "time build" column using variable $v{avgtimetobuild}, if in case (since in summary band) not nessary put evaluationtime="report" make sure it's evaluated after displaying records (if move in pagefooter required)
<textfield evaluationtime="report"> <reportelement x="150" y="0" width="150" height="20" uuid="860dd902-9791-443e-9a9d-3dedab365b23"/> <textfieldexpression><![cdata[$v{avgtimetobuild} / (24* 60 * 60 * 1000) + " days " + ($v{avgtimetobuild} / (60 * 60 * 1000)) % 24 + " hour(s), " +($v{avgtimetobuild} / (60 * 1000)) % 60 + " minute(s)"]]></textfieldexpression> </textfield>
Comments
Post a Comment