Page 1 of 1

[Solved] Sumif type function to use only positive numbers

PostPosted: Thu Mar 31, 2016 10:14 am
by amitmahajan74
Hi,

I have created a DB for daily production of articles against orders. I am stuck in a query. I get the following fields in query for the daily production report.

1. Total order
2. Production Till Date
3. Production Today
4. Total Production
5. Balance Production

Now there are times when the Total Production exceeds the Total Order and in this case the Balance Production shows as negative value. I use the query in Calc in Pivot table and in the row total at the end, it adds all the negative values too, thus giving a variation in Balance Production. The Balance production is being calculated by (sum(TotalOrder) - sum(TotalProduction)).

I would want to use a function like sumif, the above calculation with the option to give the result if it is positive only, negative result it should give the result as zero value.

Thanks,

Amit

Re: Using Sumif type function to use only positive numbers

PostPosted: Thu Mar 31, 2016 3:20 pm
by Villeroy
https://forum.openoffice.org/en/forum/d ... p?id=24572 (simple inventory)
Simple SUMIF in SQL:
Code: Select all   Expand viewCollapse view
SELECT SUM("Values") AS  FROM "Something" WHERE "Values">0

Re: Using Sumif type function to use only positive numbers

PostPosted: Fri Apr 01, 2016 7:43 am
by amitmahajan74
Thanks Villeroy, I tried using it but now it's giving the results for values where sum is >0 but eliminating the results where it is less than zero. I am attaching the complete query here, please advise ..


Code: Select all   Expand viewCollapse view
SELECT "Customer"."Customer", "OrdersLine"."Order No ID", "Order"."Season", "Category"."Category", "Order"."Facility", "Order"."Status", "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size", ( COALESCE ( "OR"."OrderQty", 0.0 ) * "Order"."Rate" ) AS "OrderValue", COALESCE ( "OR"."OrderQty", 0.0 ) AS "Order", COALESCE ( "PB"."ProdQtyBefore", 0.0 ) AS "PrTilDat", COALESCE ( "PTdy"."ProdQtyToday", 0.0 ) AS "PrToday", COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdTotal", COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdBal" FROM "OrdersLine" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "OrdQty" ) AS "OrderQty" FROM "OrdersLine" GROUP BY "Order No ID", "Size ID" ) AS "OR" ON "OrdersLine"."Order No ID" = "OR"."Order No ID" AND "OrdersLine"."Size ID" = "OR"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyBefore" FROM "OrdersLine" WHERE "ProdDate" < :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PB" ON "OrdersLine"."Order No ID" = "PB"."Order No ID" AND "OrdersLine"."Size ID" = "PB"."Size ID" LEFT OUTER JOIN ( SELECT "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment", SUM( "OrdersLine"."ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine", "Department" WHERE "OrdersLine"."ProdDate" = :RpDt AND "OrdersLine"."Dept ID" = "Department"."ID" GROUP BY "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment" ) AS "PTdy" ON "OrdersLine"."Order No ID" = "PTdy"."Order No ID" AND "OrdersLine"."Size ID" = "PTdy"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyTotal" FROM "OrdersLine" WHERE "ProdDate" <= :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PTotal" ON "OrdersLine"."Order No ID" = "PTotal"."Order No ID" AND "OrdersLine"."Size ID" = "PTotal"."Size ID" INNER JOIN "Order" ON "OrdersLine"."Order No ID" = "Order"."Order ID" INNER JOIN "Product" ON "Order"."Product ID" = "Product"."ID Number" INNER JOIN "Colour" ON "Order"."Colour ID" = "Colour"."ID Number" INNER JOIN "Size" ON "OrdersLine"."Size ID" = "Size"."ID Number" INNER JOIN "Customer" ON "Product"."Cust ID" = "Customer"."ID" INNER JOIN "Category" ON "Product"."Category ID" = "Category"."ID Number" INNER JOIN "Plan" ON "Order"."Order ID" = "Plan"."OrderNoID"

WHERE COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) > 0

and MONTH( "Plan"."Plan Month" ) = :mth AND YEAR( "Plan"."Plan Month" ) = :yr GROUP BY "OrdersLine"."Order No ID", "Order"."Season", "Order"."Facility", "Order"."Status", "Order"."Order No", "Category"."Category", "Product"."Product Name", "Colour"."Colour", "Size"."Size", "OR"."OrderQty", "PB"."ProdQtyBefore", "PTdy"."ProdQtyToday", "PTotal"."ProdQtyTotal", "Customer"."Customer", "Order"."Rate" ORDER BY "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size"

Re: Using Sumif type function to use only positive numbers

PostPosted: Fri Apr 01, 2016 10:38 am
by amitmahajan74
Got it, used casewhen in the query to use query value >0 else 0. Working now.

Thanks