buenas tardes a todos
encontre en el foro este query, y quisiera me ayudaran, por que duplica las salidas.
- 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]
pero fijese que no se por que las salidas la esta duplicando, alguien podria ayudarme, se lo agradecere, ya estoy buscando algo asi
gracias de antemano
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.