on 07-21-2016 9:54 PM
Hola buenas tardes,
Necesito una consulta que me arroje las compras contra ventas por grupo de artículos por Almacen y el inventario por Almacen por grupo de artículos,
tengo una consulta ya hecha por el inventario no es correcto, a ver si alguien me apoya en eso.
Anexo la consulta en seguida para ver si la pueden revisar.
DECLARE @VAR INT
DECLARE @INI1 DATETIME
DECLARE @FIN1 DATETIME
DECLARE @INI2 DATETIME
DECLARE @FIN2 DATETIME
SET @VAR = (SELECT TOP 1 A.TransNum FROM OINM A WHERE A.DocDate BETWEEN '[%0]' AND '[%1]' AND A.DocDate BETWEEN '[%2]' AND '[%3]')
SET @INI1 = '[%0]'
SET @FIN1 = '[%1]'
SET @INI2 = '[%2]'
SET @FIN2 = '[%3]'
SELECT DISTINCT T2.ItmsGrpNam 'Linea de Articulos',
ISNULL((SELECT SUM(V1.LineTotal) FROM INV1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI1 AND V1.DocDate <= @FIN1),0) 'Venta',
ISNULL((SELECT SUM(V1.LineTotal) FROM PCH1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI1 AND V1.DocDate <= @FIN1),0) 'Compra',
ISNULL((SELECT SUM(V1.TransValue) FROM OINM V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and T3.WhsCode = '[%4]' AND V1.DocDate < @FIN1),0) 'Inventario a la fecha inicio',
ISNULL((SELECT SUM(V1.LineTotal) FROM INV1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI2 AND V1.DocDate <= @FIN2),0) 'Venta',
ISNULL((SELECT SUM(V1.LineTotal) FROM PCH1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI2 AND V1.DocDate <= @FIN2),0) 'Compra',
ISNULL((SELECT SUM(V1.TransValue) FROM OINM V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and T3.WhsCode = '[%4]' AND V1.DocDate < @FIN2),0) 'Inventario a la fecha final',
(ISNULL((SELECT SUM(V1.LineTotal) FROM INV1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI2 AND V1.DocDate <= @FIN2),0)-
ISNULL((SELECT SUM(V1.LineTotal) FROM INV1 V1 INNER JOIN OITM V2 ON V1.ItemCode = V2.ItemCode WHERE V2.ItmsGrpCod = T2.ItmsGrpCod and V1.WhsCode = '[%4]' AND V1.DocDate >= @INI1 AND V1.DocDate <= @FIN1),0))'Diferencia Ventas'
FROM OINM T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
INNER JOIN OWHS T3 ON T0.Warehouse = T3.WhsCode
WHERE
T3.WhsCode = '[%4]'
ORDER BY
T2.ItmsGrpNam
Gracias
Saludos Johan Dyck
Johan,
En los subqueries del Inventario intenta cambiar el T3.WhsCode = [%4] por V1.Warehouse = [%4]
Saludos,
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
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.