Análisis de Ventas con Stock

[Former Member]

1. Buenos días;

Un compañero hace un par de años, resolvió este caso, pero he querido usar el query propuesto en esa ocasión y me genera un error de conversión. Queria probar ese Query y ver si me sirve para un tema que tengo pendiente

la consulta es la siguiente

DECLARE @VAR INT, @INI DATETIME, @FIN DATETIME
SET @VAR=(SELECT TOP 1 A.TransId FROM JDT1 A WHERE A.RefDate BETWEEN '[%0]' AND '[%1]')
SET @INI='[%0]'
SET @FIN='[%1]'
 
SELECT T0.[ItemCode], T0.[ItemName],
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Stock a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0) 'Stock a la fecha termino',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado',
ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)'Cantidad Vendida',
DATEDIFF (dd, @INI, @FIN)'Nº de dias segun fechas',
CASE
WHEN ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0) <= 0 THEN 0
ELSE ((ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0))/(DATEDIFF (dd, @INI, @FIN)))
END 'Venta diaria',
CASE
WHEN ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0) <= 0 THEN 0
ELSE ((ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/(((ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0))/(DATEDIFF (dd, @INI, @FIN))))))
END 'Venta futura',
ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)'Ventas',
ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN RIN1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)'Ganancia bruta',
((ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN RIN1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0))/(ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)))*100 '% Ganancia'
FROM OITM T0
WHERE /*ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.DocDate <= '[%1]'),0) != 0 AND*/
 
ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate<='[%0]'),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate<='[%0]'),0) != 0
 
ORDER BY T0.[ItemCode]
<br>

Me genera el siguiente error:

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.

Agradeceria mucho la ayuda en este asunto

slds.

[pokrakam]

Didn't understand much beyond Buenos días, but man that's a hardcore SELECT statement! 🙂

[Former Member]

no entiendo mucho de programacion, por eso me costado comprender porque da error, o mas bien entiendo q una fecha no puede convertirse en string, o esa fecha mas bien.

¿como corrijo eso, para tener un query que me traiga ventas y stock actual de los almacenes, donde el proveedor sea XXX?

gracias