How to calculate average time?

2019-07-27 04:51发布

站内文章 / MySQL
33 0 0

问题:

I am trying to figure out how to take the average of the Time To Buildof the items.

This is the equation I have used to gather the 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 are the photos of my disign and variables, along with my XML, to help explain my situation.

QUESTION: How do I fix my avgToBuild variable so that it averages all of the calculated Time To 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") AS dayToBuild,
 COMPANY."NAME" AS COMPANY_NAME,
 BOM."NUM" AS BOM_NUM,
 MO."DATECREATED" AS MO_DATECREATED,
 MO."NUM" AS MO_NUM,
 MO."DATECOMPLETED" AS MO_DATECOMPLETED,
 MOITEM."BOMID" AS MOITEM_BOMID,
 MOITEM."MOID" AS MOITEM_MOID,
 MOITEM."QTYTOFULFILL" AS MOITEM_QTYTOFULFILL,
 BOMITEMTYPE."ID" AS BOMITEMTYPE_ID,
 WO."NUM" AS WO_NUM
FROM
 "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
WHERE
BOM."NUM"=$P{bomNumber}
AND MO."DATECOMPLETED" BETWEEN $P{From} AND $P{To}
ORDER BY
 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 To 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 To 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>

回答1:

You need to calculate the average of the numeric value not the String value.

Lets calculate the 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 as in "Time to Build" column using the variable $V{avgTimeToBuild}, even if in this case (since it in summary band) its not nessary I would put evaluationTime="Report" to make sure it's evaluated after displaying all records (if you move it in pageFooter this would be 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>


标签： mysql jasper-reports
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~