Consulta Margenes

Dark_katt · 28 Mayo, 2021 12:39

Según el error, no te esta reconociendo las comillas, es el código copiado desde esta pagina?
puedes reemplazarlas?

Dark_katt · 28 Mayo, 2021 13:11

Observando tu código, tenia unos cuantos errores en el sub-select, podrías ejecutarlo y ver que tal te va?

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", 
SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
((SELECT SUM(ISNULL(Y."Quantity",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."GrossBuyPr")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL
SELECT DISTINCT T0."ItemCode", T0."ItemName",
((SELECT -SUM(ISNULL(Y."Quantity",0)) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
((SELECT (SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))*-1) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
((SELECT (SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0))*-1) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
((SELECT (SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM((ISNULL(Y."Quantity")*(ISNULL(Y."GrossBuyPr"))*-1) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

Olvir · 28 Mayo, 2021 13:20

Acabo de comprobarlo, pero da error igual

como copiar url de imagen

Dark_katt · 28 Mayo, 2021 13:22

Vamos bien, deberia quedar * 100

Olvir · 28 Mayo, 2021 13:34

Si al 100 de Ganancias le pongo *100 me genera este error, una coma parece

Syntax error or access violation;257 sql syntax error: incorrect syntax near “,”: line 5 col 232 ‘Valores definidos por usuario’ (CSHS) (at pos 527)

Dark_katt · 28 Mayo, 2021 13:39

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", 
SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))*100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."GrossBuyPr")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0)) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM((ISNULL(Y."Quantity")*(ISNULL(Y."GrossBuyPr")) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

Olvir · 28 Mayo, 2021 13:42

Corregido pero aparece otra cosa…

Syntax error or access violation;257 sql syntax error: incorrect syntax near “FROM”: line 15 col 118 ‘Valores definidos por usuario’ (CSHS) (at pos 2439)

Dark_katt · 28 Mayo, 2021 13:52

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", 
SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))*100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
ISNULL((SELECT SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM(ISNULL((Y."Quantity"))*(ISNULL(Y."GrossBuyPr"), 0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0)) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."GrossBuyPr",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
ISNULL((SELECT -SUM(ISNULL(Y."Quantity",0))*(ISNULL(Y."Price",0))-SUM((ISNULL((Y."Quantity")*(ISNULL(Y."GrossBuyPr"),0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

Olvir · 28 Mayo, 2021 15:05

Nada de nada, agradezco muchísimo tu esfuerzo de verdad

Syntax error or access violation;257 sql syntax error: incorrect syntax near “,”: line 8 col 116 ‘’ (ECM2) (at pos 1224)

Gera_Mendez · 28 Mayo, 2021 15:29

Hola @Olvir.

Acatando las normas del foro, te invito a eliminar la imagen y poner el código como texto con su respectivo formato:

Gracias.

Dark_katt · 28 Mayo, 2021 15:42

Por favor si puedes verificar todas las sentencias ISNULL

IFNULL(Y."Quantity",0)

esa es la Sintaxis

Olvir · 29 Mayo, 2021 09:13

Con la sintaxis corregida sigue dando el mismo error

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", 
SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))*100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
IFNULL((SELECT SUM(IFNULL(Y."Quantity",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
IFNULL((SELECT SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."Price",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
IFNULL((SELECT SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."GrossBuyPr",0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
IFNULL((SELECT SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."Price",0))-SUM(IFNULL((Y."Quantity"))*(IFNULL(Y."GrossBuyPr"), 0)) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL
SELECT DISTINCT T0."ItemCode", T0."ItemName",
IFNULL((SELECT -SUM(IFNULL(Y."Quantity",0)) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
IFNULL((SELECT -SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."Price",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
IFNULL((SELECT -SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."GrossBuyPr",0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
IFNULL((SELECT -SUM(IFNULL(Y."Quantity",0))*(IFNULL(Y."Price",0))-SUM((IFNULL((Y."Quantity")*(IFNULL(Y."GrossBuyPr"),0)) FROM ORIN Z INNER JOIN RIN1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

Dark_katt · 31 Mayo, 2021 18:15

El fin de semana pude dedicarle unos minutos a tu query, y por fin lo pude probar en hana studio, despues de unas pruebas, creo que lo mejor sera rehacer desde cero. Ya que el error que estaba dando era debido a los resultados del subselect.

Lo que haria seria tomar en cuenta solamente las tablas OINV, INV1 y creo que necesitarias filtrar por almacenes también.

Olvir · 1 Junio, 2021 06:50

Muchas gracias por tu ayuda, esta consulta tuve ayuda en su día para poder generarla por su complejidad.
Intentare generar una nueva, pero no veo yo mucho futuro!

Aun así gracias!

Dark_katt · 1 Junio, 2021 12:25

Buenos dias
Fijate si esto te sirve, que es lo mismo que estas haciendo pero de una manera simplificada


SELECT
b."ItemCode", b."Dscription", sum(b."Quantity") as "Cantidad", sum(b."Quantity" * b."Price") as "Ventas",
sum(b."Quantity" * b."GrossBuyPr") as "Costos", sum((b."Quantity" * b."Price") - (b."Quantity" * b."GrossBuyPr")) as "Ganancias"
from OINV a
inner join INV1 b on a."DocEntry" = b."DocEntry"
where a."DocDate" BETWEEN [%0] and [%1]
and b."Quantity" != 0 
group by b."ItemCode", b."Dscription"

Olvir · 1 Junio, 2021 13:12

Realmente esta Query realiza lo mismo que Análisis de volumen de negocio por articulo.
El realizarlo de la otra manera era el beneficio mas exacto por el precio de compra dependiendo del momento, aun así muchas gracias!

Olvir · 4 Junio, 2021 07:26

Al final he resuelto el problema con otra consulta, os la dejo por si alguien viene buscando.

SELECT T0."ItemCode", T0."Dscription", avg((T0."GrssProfit" / T0."LineTotal")*100) 
"Porcentaje Beneficio" FROM DLN1 T0  INNER JOIN ODLN T1 ON T0."DocEntry" = T1."DocEntry" 
WHERE T1."DocDate" >=[%0] and  T1."DocDate" <=[%1] AND T0."LineTotal" <> 0 GROUP BY 
T0."ItemCode", T0."Dscription"