on 07-20-2020 1:43 AM
Hi,
guys, i have a problem when doing an inventory report.
The query brings the information, but there are many duplicate lines.
I tried to limit in the OINM by the field "TransType (" 59, 20), but even so there are several duplicate lines and with that making it difficult to extract the information.
I ask the specialists for help.
T2."TransType",T2."DocDate", T2."InQty", T2."OutQty", T4."WhsCode", T3."WhsName" AS "Nome do depósito", T1."ItemCode", T1."ItemName", T0."ItmsGrpNam", T1."InvntryUom", T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", T4."AvgPrice"
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 OITM T5 ON T5."ItemCode" = T4."ItemCode"
WHERE
T2."TransType" in (59,20) AND
T1."ItemCode" = [%0]
Thankful!
Regards
Junior
Hi Junior,
Try it
select top 10 T2."TransType",T2."DocDate", SUM(T2."InQty") AS "InQty", SUM(T2."OutQty") AS "OutQty", T4."WhsCode", T3."WhsName" AS "Nome do depósito", T1."ItemCode", T1."ItemName", T0."ItmsGrpNam", T1."InvntryUom", T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao", AVG(T4."AvgPrice") AS "AvgPrice" 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 OITM T5 ON T5."ItemCode" = T4."ItemCode" WHERE T2."TransType" in (59,20) 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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Greetings Anuj Kumar!
It helped me a lot with the TOP 10, I put TOP100 to reach my goal.
There is only one thing I forgot to mention that the field (T4.OnHand) and putting it again I duplicate the information.
The OITW field (T4.OnHand) is important because I need the accumulated total.
Do you have any idea?
Regards,
Junior.
Pls try it
select T2."TransType",T2."DocDate", T4."WhsCode", T3."WhsName" AS "Nome do depósito", SUM(T2."InQty") AS "InQty", SUM(T2."OutQty") AS "OutQty",T4."OnHand", T1."ItemCode", T1."ItemName", T0."ItmsGrpNam", T1."InvntryUom",
T1."U_RSD_PesoBruto", T1."U_RSD_UN_Conversao",
AVG(T4."AvgPrice") AS "AvgPrice"
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" and T4."ItemCode"=T1."ItemCode"
INNER JOIN OITM T5 ON T5."ItemCode" = T4."ItemCode"
WHERE T2."TransType" in (59,20) 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"
,T4."OnHand"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
110 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.