Total cumulative in the IOMN

[former_member691564]

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"

[kothandaraman_nagarajan · Accepted solution]

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

[former_member691564 · Accepted solution]

Hi,

How are you Nagarajan?
Until this calculation I had arrived, but the problem is that in this way it brings wrong values.

Logic:
If line 1 has 2 entries, and line 2 has 3 more, then total stock is = 5.

Regards,

Junior

[kothandaraman_nagarajan]

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

[former_member691564]

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

[former_member691564]

Hi,
Any more tips?

Regards,