on 05-02-2014 9:18 PM
Buenas tardes
me podrias apoyar con una consulta donde me de el resultado
de inventario inicial , entradas- salidas- inventario final, entre 2 fechas determinadas
gracias de antemano
Te proporciono este Query para que lo pruebes solo adecuas a los almacenes que necesitas.
la @FechaInicial y @FechaFinal le pones tu rango, la fecha que se encuentra alli constante en el query es la fecha del inicio de nuestra implementacion. Prueba y me comentas.
saludos cordiales
SELECT T0.[ItemCode]
,CAST(SUBSTRING(t0.Itemcode, 1, 3) AS INT) ItmsGrpCod,
t2.ItmsGrpCod as grupos
,T2.[ItemName]
,ROUND(t2.avgprice,5,1)costo
,T4.inv as 'Inv_in_mp'
,T5.inv as 'Inv_in_PA'
,T6.inv as 'Inv_in_pesas'
,T7.inv as 'Inv_in_2989'
,T8.inv as 'Inv_fin_mp'
,T9.inv as 'Inv_fin_PA'
,T10.inv as 'Inv_fin_pesas'
,T11.inv as 'Inv_fin_2989'
,T8.costo as 'costo_mp'
,T9.costo as 'costo_PA'
,T10.costo as 'costo_pesas'
,T11.costo as 'costo_2989'
,T13.entradas as 'entradas'
,t14.producido as 'producido'
FROM OITW T0
INNER JOIN OWHS T1 ON T0.WhsCode = T1.WhsCode
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
INNER JOIN OINM T3 ON t0.ItemCode = t3.ItemCode
--INICIALES
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaInicial AND Y.Warehouse = 'MP'
GROUP BY Y.ItemCode, Y.Warehouse
)T4 ON T4.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaInicial AND Y.Warehouse = 'PA'
GROUP BY Y.ItemCode, Y.Warehouse
)T5 ON T5.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaInicial AND Y.Warehouse = 'PESAS'
GROUP BY Y.ItemCode, Y.Warehouse
)T6 ON T6.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaInicial AND Y.Warehouse = '29-89'
GROUP BY Y.ItemCode, Y.Warehouse
)T7 ON T7.ItemCode = T2.ItemCode
--FINALES
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv,
CAST(ROUND((SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)),2,1)AS FLOAT) costo
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaFinal AND Y.Warehouse = 'MP'
GROUP BY Y.ItemCode, Y.Warehouse
)T8 ON T8.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv,
CAST(ROUND((SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)),2,1)AS FLOAT) costo
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaFinal AND Y.Warehouse = 'PA'
GROUP BY Y.ItemCode, Y.Warehouse
)T9 ON T9.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv,
CAST(ROUND((SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)),2,1)AS FLOAT) costo
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaFinal AND Y.Warehouse = 'PESAS'
GROUP BY Y.ItemCode, Y.Warehouse
)T10 ON T10.ItemCode = T2.ItemCode
FULL JOIN
(SELECT Y.ItemCode, Y.Warehouse,
CAST(ROUND((SUM(Y.InQty)-SUM(Y.OutQty)),2 ,1) AS FLOAT) inv,
CAST(ROUND((SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)),2,1)AS FLOAT) costo
FROM OINM Y
WHERE Y.DocDate between '20130101' and @FechaFinal AND Y.Warehouse = '29-89'
GROUP BY Y.ItemCode, Y.Warehouse
)T11 ON T11.ItemCode = T2.ItemCode
--entradas
FULL JOIN(
SELECT DISTINCT t1.itemcode, SUM(T1.Quantity) entradas
FROM OPDN T0
INNER JOIN PDN1 T1 ON T1.DocEntry = T0.DocEntry
and t0.docdate between @FechaInicial and @FechaFinal
and t0.CANCELED = 'N'
group by t1.itemcode
)T13 ON T13.ItemCode = T2.ItemCode
--producido
FULL JOIN(
SELECT Y.ItemCode,
y.dscription, Y.Warehouse,
CAST(ROUND((SUM(Y.outQty)),2 ,1) AS FLOAT) producido
FROM OINM Y
WHERE Y.DocDate between @FechaInicial and @FechaFinal AND Y.Warehouse = 'PA'
and (CAST(SUBSTRING(Y.Itemcode, 1, 2) AS INT)) = 30
GROUP BY Y.ItemCode, Y.dscription, Y.Warehouse
)T14 ON T14.ItemCode = T2.ItemCode
WHERE (t1.WhsCode = 'MP' OR t1.WhsCode = 'PA' OR t1.WhsCode = 'PESAS' OR t1.WhsCode = '29-89') and t3.DocDate between '20130101' and @FechafINAL
and t2.itmsgrpcod != '219' and T0.[ItemCode] != '105060'
group by
T0.[ItemCode],
T2.[ItemName],
t2.ItmsGrpCod,
T2.ItemCode,
t2.avgprice,
T4.inv,
T5.inv,
T6.inv,
T7.inv,
T8.inv,
T9.inv,
T10.inv,
T11.inv,
T8.costo,
T9.costo,
T10.costo,
T11.costo,
T13.entradas,
t14.producido
ORDER BY ItmsGrpCod,T0.[ItemCode]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola,
Una manera sería la siguiente.
Declare @Fecha as datetime
Set @Fecha = '20140501' --Primer dia del mes
select Sum(CASE When DocDate
Sum(CASE When DocDate >= @Fecha and DocDate < DATEADD(m,1,@Fecha) then InQty else 0 end) Entradas,
Sum(CASE When DocDate >= @Fecha and DocDate < DATEADD(m,1,@Fecha) then OutQty else 0 end) Salidas,
Sum(CASE When DocDate < DATEADD(m,1,@Fecha) then InQty - OutQty else 0 end) InventarioFinal
from OINM
Where ItemCode = 'A00001'
Donde todo se calcula en el momento y se basa en la tabla de movimientos.
Fecha se define como una variable la cual debe ser el primer dia del mes a consultar.
Y el ItemCode se puede variar al articulo deseado, o bien agregar un group by ItemCode para sacar todos los articulos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.