cancel
Showing results for 
Search instead for 
Did you mean: 

query de inventario

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

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]

former_member413321
Contributor
0 Kudos

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.