Subtraction between 2 conditions in SQL/iReport

2019-09-08 04:49发布

站内文章 / 前端开发
32 0 0

问题:

I am writing a report and everything is almost completed! The only issue I have left is one I have been unable to solve.

The report is supposed to show items by location/zipcode within a certain date range. It then uses a GROUP to pair up all of the productNums and their QTY. Their are only 2 conditions I have in place at the moment which is

 Where
(soitem.typeid = 10 or soitem.typeid = 20)

This is so that I only want to pick up Salesorder items that are sales (typeid = 10) or credit returns (typeid = 20).

My problem is that I have the dollar amounts printing off fine. It deducts the sale amount when there is a return but for what ever reason it is still treating the QTY as a positive so it says someone sold 12 items to someone when in fact they did, but the customer returned all 12.

What is showing up is:

But this is incorrect because for the first item the QTY should be ZERO instead of 12. The Sales Amount is correct but the itemQTY is not working correctly. Should I use a conditional statement in my SQL to fix this issue? Or is there another way?

Here is my SQL and JRXML code.

SELECT
    soitem.PRODUCTNUM, so.SHIPTOZIP as ZIP, soitem.QTYFULFILLED as QTY,so.SHIPTOSTATEID as stateID, COALESCE(stateconst.code,'') AS stateabbr, so.DATECOMPLETED as soDATE, soitem.TOTALPRICE as totalAmount, soitem.typeid, company.NAME AS companyName
FROM so
    JOIN STATECONST on STATECONST.ID = so.SHIPTOSTATEID
    JOIN soitem on soitem.SOID = so.ID,
    "COMPANY" COMPANY
WHERE
    so.DATECOMPLETED between $P{From} and $P{To}
    AND (UPPER(COALESCE(stateconst.code,'')) = UPPER($P{State}) OR so.SHIPTOZIP = $P{ZipCode})
    AND soitem.PRODUCTNUM != 'SHIPPING'
    AND soitem.PRODUCTNUM != 'SAMPLE'
    AND soitem.PRODUCTNUM != 'SHIP TO CONTACT'
    AND (soitem.typeid = 10 or soitem.typeid = 20)
ORDER BY 1

JRXML

<?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="SalesOrderItemsByZip" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d4cb0e75-8b4b-4aba-9324-9be9518ac4c7">
    <property name="ireport.zoom" value="1.5"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="From" class="java.util.Date"/>
    <parameter name="To" class="java.util.Date"/>
    <parameter name="ZipCode" class="java.lang.String" isForPrompting="false"/>
    <parameter name="State" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
    soitem.PRODUCTNUM, so.SHIPTOZIP as ZIP, soitem.QTYFULFILLED as QTY,so.SHIPTOSTATEID as stateID, COALESCE(stateconst.code,'') AS stateabbr, so.DATECOMPLETED as soDATE, soitem.TOTALPRICE as totalAmount, soitem.typeid, company.NAME AS companyName
FROM so
    JOIN STATECONST on STATECONST.ID = so.SHIPTOSTATEID
    JOIN soitem on soitem.SOID = so.ID,
    "COMPANY" COMPANY
WHERE
    so.DATECOMPLETED between $P{From} and $P{To}
    AND (UPPER(COALESCE(stateconst.code,'')) = UPPER($P{State}) OR so.SHIPTOZIP = $P{ZipCode})
    AND soitem.PRODUCTNUM != 'SHIPPING'
    AND soitem.PRODUCTNUM != 'SAMPLE'
    AND soitem.PRODUCTNUM != 'SHIP TO CONTACT'
    AND (soitem.typeid = 10 or soitem.typeid = 20)
ORDER BY 1]]>
    </queryString>
    <field name="PRODUCTNUM" class="java.lang.String"/>
    <field name="ZIP" class="java.lang.String"/>
    <field name="QTY" class="java.lang.Double"/>
    <field name="STATEID" class="java.lang.Integer"/>
    <field name="STATEABBR" class="java.lang.String"/>
    <field name="SODATE" class="java.sql.Timestamp"/>
    <field name="TOTALAMOUNT" class="java.lang.Double"/>
    <field name="TYPEID" class="java.lang.Integer"/>
    <field name="COMPANYNAME" class="java.lang.String"/>
    <variable name="sumTotalQuantity" class="java.lang.Double" resetType="Group" resetGroup="product" calculation="Sum">
        <variableExpression><![CDATA[$F{QTY}]]></variableExpression>
    </variable>
    <variable name="sumTotalSales" class="java.lang.Double" resetType="Group" resetGroup="product" calculation="Sum">
        <variableExpression><![CDATA[$F{TOTALAMOUNT}]]></variableExpression>
    </variable>
    <variable name="sumGrandTotalQuantity" class="java.lang.Double" calculation="Sum">
        <variableExpression><![CDATA[$F{QTY}]]></variableExpression>
        <initialValueExpression><![CDATA[0d]]></initialValueExpression>
    </variable>
    <variable name="sumGrandTotalSales" class="java.lang.Double" calculation="Sum">
        <variableExpression><![CDATA[$F{TOTALAMOUNT}]]></variableExpression>
        <initialValueExpression><![CDATA[0d]]></initialValueExpression>
    </variable>
    <group name="product">
        <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression>
        <groupFooter>
            <band height="50">
                <textField evaluationTime="Group" evaluationGroup="product" pattern="###0">
                    <reportElement x="175" y="0" width="50" height="20" uuid="915c3f87-127a-4ec7-8b54-136612672c46"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$V{sumTotalQuantity}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="375" y="0" width="75" height="20" uuid="4d241f98-7272-4c6e-bdc4-d395de916ede"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$F{ZIP}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="479" y="0" width="75" height="20" uuid="3885c024-0db1-44f3-83c0-7f82a9d43a6c"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$F{STATEABBR}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="0" y="0" width="150" height="50" uuid="16730885-6808-471e-b14c-cce62c865307"/>
                    <textFieldExpression><![CDATA[$F{PRODUCTNUM}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Group" evaluationGroup="product" pattern="¤ #,##0.00">
                    <reportElement x="250" y="0" width="100" height="20" uuid="1cc304ae-ca15-40d4-ab0c-122b6909677b"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$V{sumTotalSales}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <title>
        <band height="50" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="555" height="25" uuid="02aceea0-791a-430c-9bb4-b6c0a62bb29a"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="16" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{COMPANYNAME}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="25" width="555" height="25" uuid="044c5bea-f482-4329-93d0-64433f0714a9"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="15" isBold="true"/>
                </textElement>
                <text><![CDATA[Sales Order Items By Zip]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="20">
            <staticText>
                <reportElement x="301" y="0" width="20" height="20" uuid="e3eeac2b-12bb-43af-b133-1faeabdb0260"/>
                <textElement textAlignment="Center">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[-]]></text>
            </staticText>
            <textField pattern="MM/dd/yyyy">
                <reportElement x="226" y="0" width="75" height="20" uuid="1c3c18a3-d58c-41a9-b157-9baecdc04a70"/>
                <textElement textAlignment="Right">
                    <font size="14"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{From}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="1" y="0" width="225" height="20" uuid="98522dcd-468c-4fa9-829c-e3a9349a07a4"/>
                <textElement textAlignment="Right">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[Date Range:]]></text>
            </staticText>
            <textField pattern="MM/dd/yyyy">
                <reportElement x="321" y="0" width="75" height="20" uuid="29ec56c7-3aff-4119-9fcb-f533608bc9ab"/>
                <textElement>
                    <font size="14"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{To}]]></textFieldExpression>
            </textField>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="25" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="150" height="20" uuid="b2e517f0-bd17-4b5c-a958-a6f71ecc6f3d"/>
                <textElement>
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Product #]]></text>
            </staticText>
            <staticText>
                <reportElement x="175" y="0" width="50" height="20" uuid="fffdc077-159e-446e-b110-a8c3f8b2e3a0"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Qty]]></text>
            </staticText>
            <staticText>
                <reportElement x="375" y="0" width="75" height="20" uuid="fd05ad98-c3d1-43ff-a56a-9cfe47c56a7e"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[ZIP]]></text>
            </staticText>
            <staticText>
                <reportElement x="479" y="0" width="75" height="20" uuid="a15d76c6-9a14-4089-bcb8-a6f58122004f"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[State]]></text>
            </staticText>
            <staticText>
                <reportElement x="250" y="0" width="100" height="20" uuid="30e32f6b-9477-41f9-8eef-ef27995f5cb4"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Sales Amount]]></text>
            </staticText>
            <line>
                <reportElement x="0" y="24" width="555" height="1" uuid="eaf8a705-5cbb-4809-9345-517f1128b919"/>
            </line>
        </band>
    </columnHeader>
    <summary>
        <band height="45">
            <staticText>
                <reportElement x="0" y="5" width="126" height="20" uuid="dc471d6e-e9a6-4351-9464-07b485708f0d"/>
                <textElement verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Grand Total Quantity:]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="25" width="126" height="20" uuid="0d772f98-e1ef-449e-9dd0-7df1c0e4071d"/>
                <textElement verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Grand Total Sales:]]></text>
            </staticText>
            <textField evaluationTime="Report" pattern="#,##0">
                <reportElement x="126" y="5" width="75" height="20" uuid="450ee5e0-459b-4f7b-8990-68db5732c39d"/>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{sumGrandTotalQuantity}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report" pattern="¤ #,##0.00">
                <reportElement x="126" y="25" width="75" height="20" uuid="9286c874-dc26-4aff-bf27-15b2dea28b9c"/>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{sumGrandTotalSales}]]></textFieldExpression>
            </textField>
            <line>
                <reportElement x="0" y="0" width="555" height="1" uuid="01b925b2-ff3c-4df3-a5bd-ca11defc4bf2"/>
            </line>
        </band>
    </summary>
</jasperReport>
标签： sql jasper-reports
举报

收藏的人(0)

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