Skip to Content

Total cumulative in the IOMN

Hi,

Guys, I crashed in a cumulative calculation being in the OINM table.
Although there is a lot of information on the forum on the subject, I haven't been able to resolve this issue yet and with that I ask for your help.
The question is:
I need to calculate (T2. "InQty" - T2. "OutQty") with this bringing me the calculated value and in the following lines If:
Input = SUM
Output = Subtract

I saw in some post that you need to declare (@declare) but I still lack experience.

T2."DocDate", T4."WhsCode", T3."WhsName", T0."ItmsGrpNam",T1."ItemCode", T1."ItemName",
T1."InvntryUom",(T2."InQty") AS "Qtd In",(T2."OutQty") AS "Qtd Out", AVG((T2."InQty") - (T2."OutQty")) As "Total Stock", T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", 
AVG(t2."Price") AS "Price"

FROM OITB T0     
INNER JOIN OITM T1 ON T1."ItmsGrpCod" = T0."ItmsGrpCod" 
INNER JOIN OINM T2 ON T2."ItemCode" = T1."ItemCode"
INNER JOIN OWHS T3 ON T3."WhsCode" = T2."Warehouse" 
INNER JOIN OITW T4 ON T4."WhsCode" = T3."WhsCode"
--INNER JOIN OITW T5 ON T5."ItemCode" = T2."ItemCode" 

WHERE 
T2."TransType" in (13,14,15,16,18,19,20,59,60,67,69,162,310000001) AND
T1."ItemCode" = '[%0]' 

GROUP BY T2."TransType",T2."DocDate",T4."WhsCode",T3."WhsName", T1."ItemCode", T1."ItemName",
T0."ItmsGrpNam", T1."InvntryUom",T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", T2."InQty",T2."OutQty"

Order by T2."DocDate"
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jul 20, 2020 at 11:33 PM

    Hi,

    Try this query,

    T2."DocDate", T4."WhsCode", T3."WhsName", T0."ItmsGrpNam",T1."ItemCode", T1."ItemName", T1."InvntryUom",(T2."InQty") AS "Qtd In",(T2."OutQty") AS "Qtd Out", AVG((T2."InQty") - (T2."OutQty")) As "Total Stock", T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", AVG(t2."Price") AS "Price",

    SUM((T2."InQty") - (T2."OutQty"))

    FROM OITB T0 INNER JOIN OITM T1 ON T1."ItmsGrpCod" = T0."ItmsGrpCod" INNER JOIN OINM T2 ON T2."ItemCode" = T1."ItemCode" INNER JOIN OWHS T3 ON T3."WhsCode" = T2."Warehouse" INNER JOIN OITW T4 ON T4."WhsCode" = T3."WhsCode" --INNER JOIN OITW T5 ON T5."ItemCode" = T2."ItemCode"

    WHERE T2."TransType" in (13,14,15,16,18,19,20,59,60,67,69,162,310000001) AND T1."ItemCode" = '[%0]'

    GROUP BY T2."TransType",T2."DocDate",T4."WhsCode",T3."WhsName", T1."ItemCode", T1."ItemName", T0."ItmsGrpNam", T1."InvntryUom",T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", T2."InQty",T2."OutQty" Order by T2."DocDate"

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 25, 2020 at 10:57 AM

    Hi,
    Any more tips?

    Regards,

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 25, 2020 at 11:04 AM

    Hi,

    Are you looking for this,

    T2."DocDate", T4."WhsCode", T3."WhsName", T0."ItmsGrpNam",T1."ItemCode", T1."ItemName", T1."InvntryUom",(T2."InQty") AS "Qtd In",(T2."OutQty") AS "Qtd Out", AVG((T2."InQty") - (T2."OutQty")) As "Total Stock", T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", AVG(t2."Price") AS "Price",

    SUM(T2."InQty") - SUM(T2."OutQty")

    FROM OITB T0 INNER JOIN OITM T1 ON T1."ItmsGrpCod" = T0."ItmsGrpCod" INNER JOIN OINM T2 ON T2."ItemCode" = T1."ItemCode" INNER JOIN OWHS T3 ON T3."WhsCode" = T2."Warehouse" INNER JOIN OITW T4 ON T4."WhsCode" = T3."WhsCode" --INNER JOIN OITW T5 ON T5."ItemCode" = T2."ItemCode"

    WHERE T2."TransType" in (13,14,15,16,18,19,20,59,60,67,69,162,310000001) AND T1."ItemCode" = '[%0]'

    GROUP BY T2."TransType",T2."DocDate",T4."WhsCode",T3."WhsName", T1."ItemCode", T1."ItemName", T0."ItmsGrpNam", T1."InvntryUom",T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", T2."InQty",T2."OutQty" Order by T2."DocDate"

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,
      Analyzing it better I could see that I was using the aggregator (DocDate), with that the query was calculating all the launches of the day.
      Instead of "DocDate" I did aggregation by launching "TransNum" from OINM.

      SUM((T2."InQty") - (T2."OutQty"))OVER(ORDER BY T2."TransNum")

      Case solved.

      Regards,

      Junior

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.