on 07-24-2012 3:47 PM
Hola a todos, me están solicitando un reporte de inventarios que tenga los datos que ven el archivo adjunto. He estado revisando la tabla ATIM que según yo es la que guarda el histórico del articulo, pero por alguna razón no me aparecen los movimientos del año en curso... alguien tiene una idea de que tablas me pueden ayudar para armar el reporte...
Gracias.
Hola, tenía un query parecido, le cambie unas cosas y ya quedo
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.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Valor a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<=@FIN)),0) 'Entradas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.OutQty<=0 AND (Y.DocDate>@INI AND Y.DocDate<=@FIN)),0) 'Importe',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<=@FIN)),0) 'Salidas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.InQty<=0 AND (Y.DocDate>@INI AND Y.DocDate<=@FIN)),0) 'Importe',
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)/ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Precio Unit',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado'
FROM OITM T0
ORDER BY T0.[ItemCode]
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Eso es porque encontró articulos con stock 0 para calcular el precio unitario
Le agregue un filtro ahora
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.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Valor a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Entradas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.OutQty<=0 AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Importe',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Salidas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.InQty<=0 AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Importe',
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)/ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Precio Unit',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado'
FROM OITM T0
WHERE (ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0))>0
ORDER BY T0.[ItemCode]
Atte.
OK si así ya jala, oye abusando de ti el costo que debe tomar en cuenta para las salidas es el costo promedio mas el .03%, como le puedo meter esa formula ??... segun yo le meti la formula del * .03 pero me marca error ...y crees que le pueda agregar el parámetro de solicitar el almacén asi como solicita las fechas de inicio y final ??
gracias
si ya le había metido un valor así pero el monto que me arroja sale mal tu crees, creo q es porq el importe es negativo o algo así, deja le muevo a la formula para ver si le doy al clavo.
el porcentaje es .03 lo que pasa es que venden casi al mismo costo porque las "ventas" las hacen a otra empresa del grupo, pero por ley fiscal no se les permite vender al mismo costo lo mínimo que deben aumentar es ese .03% por eso metieron esa formula rara..
oye y lo del almacen crees que si se pueda meter ??..
gracias
si, osea que me pida el almacén así como pide fechas de inicio y final.
ya me salio el importe de salidas con el .03% mas, así quedo el query :
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.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Valor a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Entradas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.OutQty<=0 AND (Y.DocDate>@INI AND
Y.DocDate<@FIN)),0) 'Importe',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Salidas',
((ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0)) *
(ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/ISNULL((SELECT
(SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)+ISNULL((SELECT SUM(TransValue)*0.03
FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE
Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0))) 'importe salidas',
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)/ISNULL((SELECT
(SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Precio Unit',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado'
FROM OITM T0
WHERE (ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0))>0
ORDER BY T0.[ItemCode]
upps me equivoque de query este es :
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.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Valor a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Entradas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.OutQty<=0 AND (Y.DocDate>@INI AND
Y.DocDate<@FIN)),0) 'Importe',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0) 'Salidas',
((ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN)),0)) *
(ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/ISNULL((SELECT
(SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)+ISNULL((SELECT SUM(TransValue)*0.03
FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE
Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0))) 'importe salidas',
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)/ISNULL((SELECT
(SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Precio Unit',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado'
FROM OITM T0
WHERE (ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0))>0
ORDER BY T0.[ItemCode]
sorry
Try this
DECLARE @VAR INT, @INI DATETIME, @FIN DATETIME, @ALM NVARCHAR(8)
SET @VAR=(SELECT TOP 1 A.[TransNum] FROM [dbo].[OINM] A WHERE A.DocDate BETWEEN '[%0]' AND '[%1]' AND A.Warehouse='[%2]')
SET @INI='[%0]'
SET @FIN='[%1]'
SET @ALM='[%2]'
SELECT T0.[ItemCode], T0.[ItemName], @ALM 'Almacen',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI AND Y.Warehouse=@ALM),0) 'Stock a la fecha inicio',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI AND Y.Warehouse=@ALM),0) 'Valor a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN) AND Y.Warehouse=@ALM),0) 'Entradas',
ISNULL((SELECT (SUM(Y.TransValue)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.OutQty<=0 AND (Y.DocDate>@INI AND Y.DocDate<@FIN) AND Y.Warehouse=@ALM),0) 'Importe',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN) AND Y.Warehouse=@ALM),0) 'Salidas',
((ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND (Y.DocDate>@INI AND Y.DocDate<@FIN) AND Y.Warehouse=@ALM),0))*
(ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)/
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)+
ISNULL((SELECT SUM(TransValue)*0.03 FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)/
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0))) 'importe salidas',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0) 'Stock a la fecha termino',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)/ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)'Precio Unit',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0)'Stock Valorizado'
FROM OITM T0
WHERE (ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN AND Y.Warehouse=@ALM),0))>0
ORDER BY T0.[ItemCode]
Saludos
User | Count |
---|---|
107 | |
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.