on 05-05-2016 11:28 PM
Buenas tardes foro
estoy realizando una consulta para el área de materias primas en la cual me piden que por medio de la consulta les de su stock, entradas, y transferencias.
el punto es que me piden también que les de el stock del dia anterior a determinada hora.
ejemplo yo quiero el stock del dia de hoy a las 7:30 AM y en la consulta tambien me tiene que traer el stock del dia anterio de esa misma hora y con todos los movimientos de esa hora a la hora de corte
esto es la parte que llevo
declare @i datetime, @f datetime
set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')
set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
SELECT DISTINCT T.ItemCode, T.ItemName,A.OnHand,
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].PDN1 B INNER JOIN [dbo].OPDN A1 ON B.DocEntry = A1.DocEntry
WHERE B.ItemCode=T.ItemCode AND A1.DocDate >= @i AND A1.DocDate<= @f and A1.Canceled='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].RPD1 B INNER JOIN [dbo].ORPD A2 ON B.DocEntry = A2.DocEntry
WHERE B.ItemCode=T.ItemCode AND A2.DocDate >= @i AND A2.DocDate<= @f and A2.Canceled='N'),0) as'Devoluciones',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OWTR X INNER JOIN [dbo].WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >=@i AND X.DocDate<= @f AND Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OIGE X INNER JOIN [dbo].IGE1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >=@i AND X.DocDate<= @f and Y.WhsCode = 'WIP' AND Y.AcctCode = '1200.2.1' AND X.Comments LIKE '%Nº-OT%'),0) as'Cantidad Consumida'
FROM OITM T Inner JOin OITW A on T.ItemCode = A.ItemCode
where T.SellItem = 'N' and T.PrchSeItem = 'Y' AND A.WhsCode = 'MP'
GROUP BY T.ItemCode, T.ItemName,A.OnHand
ORDER BY T.ItemCode
pero lo que no se es como traer por hora los movimientos y el historial de mis inventarios
buenas tardes estoy ocupando DocTime
esta es mi consulta
/* Select * From [dbo].[OWTR] T0 */
Declare @FecIni DateTime
Declare @FecFin DateTime
Declare @HorIni Int
Declare @HorFin Int
/* Where */
select @FecIni /* T0.[DocDate] */= '[%0]'
/* And */
Select @FecFin /* T0.[DocDate] */= '[%1]'
/* And */
Select @HorIni /* T0.[DocTime] */= '[%2]'
/* And */
Select @HorFin /* T0.[DocTime] */= '[%3]'
SELECT DISTINCT T.ItemCode, T.ItemName,A.OnHand,
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].PDN1 B INNER JOIN [dbo].OPDN A1 ON B.DocEntry = A1.DocEntry
WHERE B.ItemCode=T.ItemCode AND A1.DocDate >= @FecIni AND A1.DocDate<= @FecFin and A1.DocTime <= @HorIni and A1.DocTime <= @HorFin and A1.Canceled='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].RPD1 B INNER JOIN [dbo].ORPD A2 ON B.DocEntry = A2.DocEntry
WHERE B.ItemCode=T.ItemCode AND A2.DocDate >= @FecIni AND A2.DocDate<= @FecFin and A2.DocTime <= @HorIni and A2.DocTime <= @HorFin and A2.Canceled='N'),0) as'Devoluciones',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OWTR X INNER JOIN [dbo].WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >= @FecIni AND X.DocDate<= @FecFin AND DocTime <= @HorIni and DocTime <= @HorFin and Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
A.OnHand + IsNull((SELECT Sum(B.Quantity)
FROM [dbo].PDN1 B INNER JOIN [dbo].OPDN A1 ON B.DocEntry = A1.DocEntry
WHERE B.ItemCode=T.ItemCode AND A1.DocDate >= @FecIni AND A1.DocDate<= @FecFin and A1.DocTime <= @HorIni and A1.DocTime <= @HorFin and A1.Canceled='N'),0) -
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].RPD1 B INNER JOIN [dbo].ORPD A2 ON B.DocEntry = A2.DocEntry
WHERE B.ItemCode=T.ItemCode AND A2.DocDate >= @FecIni AND A2.DocDate<= @FecFin and A2.DocTime <= @HorIni and A2.DocTime <= @HorFin and A2.Canceled='N'),0) -
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OWTR X INNER JOIN [dbo].WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >= @FecIni AND X.DocDate<= @FecFin AND DocTime <= @HorIni and DocTime <= @HorFin and Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Stock historial'
/*ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OIGE X INNER JOIN [dbo].IGE1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >=@FecIni AND X.DocDate<= @FecFin and Y.WhsCode = 'WIP' AND Y.AcctCode = '1200.2.1' AND X.Comments LIKE '%Nº-OT%'),0) as'Cantidad Consumida'*/
FROM OITM T Inner JOin OITW A on T.ItemCode = A.ItemCode
where T.SellItem = 'N' and T.PrchSeItem = 'Y' AND A.WhsCode = 'MP'
GROUP BY T.ItemCode, T.ItemName,A.OnHand
ORDER BY T.ItemCode
lo que requiero es que me el stock que tendría si le resto lo entregado y lo devuelto con ese tiempo que le estoy dando
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola
yo utilizaría las tablas OILM e ILM1 para realizar la consulta. Son las tablas de log de inventario.
Un saludo
Agustín Marcos Cividanes
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
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.