Consulta de inventario por hora y fecha

Asanchez · 10 Mayo, 2016 16:36

Buenos días foro

estoy realizando una consulta para sacar mis inventarios y movimientos por hora y por fecha.

ya tengo la consulta la cual si me muestra los movimientos. pero por ejemplo si quiero saber los movimientos de las 22:00 horas asta las 7:30 horas me manda cantidades en 0 y si las hago de las 7:30 a las 22:00 horas si me las da.

es decir cuando selecciono un rango de un solo día si me muestra los daos pero si selecciono un horario que comienza en un día y termina en el siguiente no me muestra nada.

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].RPD1 B INNER JOIN [dbo].ORPD A2 ON B.DocEntry = A2.DocEntry
WHERE B.ItemCode=T.ItemCode AND A2.DocDate BETWEEN @FecIni AND @FecFin and A2.DocTime BETWEEN @HorIni and @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 BETWEEN @FecIni AND @FecFin AND DocTime BETWEEN @HorIni and @HorFin and Y.FromWhsCod = 'MP' AND  Y.WhsCode = 'WIP'),0)as 'stock historial',
 
 
 
 
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 BETWEEN @FecIni AND @FecFin and A1.DocTime BETWEEN @HorIni and @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 BETWEEN @FecIni AND @FecFin and A2.DocTime BETWEEN @HorIni and @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 BETWEEN @FecIni AND @FecFin AND DocTime BETWEEN @HorIni and @HorFin and Y.FromWhsCod = 'MP' AND  Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
A.OnHand as 'Inventario'
/*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

SidV · 10 Mayo, 2016 16:57

@Asanchez, por la “consulta” en SQL entiendo que estás trabajando con SAP B1, verdad? Recuerda dar todos los detalles, así las personas podrán ayudarte mejor la próxima vez.

Muevo este tema a #consultas-sap:sap-business-one

Por favor, pasa por Presentaciones y contanos sobre vos, con qué trabajas, etc.

Nelson1623 · 10 Mayo, 2016 20:09

Porque no lo hace convirtiendo todo a minutos .

GabrielGS · 11 Mayo, 2016 13:17

Veamos, primero vamos a aclarara que la sentencia DocTime BETWEEN @HorIni AND @HorFin es lo mismo a decir DocTime >= @HorIni AND DocTime <= @HorFin . Por lo tanto, si nos vamos a la teoria de conjuntos (en lo que esta basado todo criterio de seleccion y aperadores relacionales), tenemos que si colocas @HorIni = 2000 y @HorFin = 730 aplicando el Between jamas te mostrara ningun resultado, sencillamente porque no existen elementos que cumplan ambas condiciones (Estamos comparando unicamente valores de horas).

Para solucionarlo, deberias usar el formato DateTime usando la Dupla Fecha + Hora de Inicio y Fecha + Hora Fin, cada uno de forma conjunta . Por ejemplo,usariamos el DateTime '10/05/2016 22:00:00' como valor Inicial y '11/05/2016 07:00:00' como valor final, alli si funcionaria correctamente la condicion Between, claro que tambien deberas construir correctamente el formato DocDate + DocTime. Algo asi:

CAST(CONVERT (char(10),DocDate, 120)+' '+
LTRIM(LEFT(RIGHT(REPLICATE('0',4)+CAST(DocTime as nvarchar(4)),4),2))+':'+ RIGHT(CAST(DocTime as nvarchar(4)),2)+':00.000' as DateTime)

Tambien puedes usar los formatos UTC o Epoch para contruir la dupla Fecha+Hora.

Saludos.

Asanchez · 11 Mayo, 2016 22:30

ok muchas gracias.

tengo algo así en mi código


/* Select * From [dbo].[OWTR] T0 */
Declare @FechaHoraInicio varchar 
Declare @FechaHoraFin varchar
/* Where */ 
select @FechaHoraInicio /* T0.[DocDate]*/= '[%0]' 
/* And */ 
Select @FechaHoraFin /* T0.[DocDate] */= '[%1]' 

SELECT DISTINCT T.ItemCode, T.ItemName,

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.Canceled='N' AND Convert(nvarchar(8),A1.DocDate,112) + Right('0000' + convert(nvarchar(4),A1.DocTime),4) between @FechaHoraInicio and @FechaHoraFin),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.Canceled='N' AND Convert(nvarchar(8),A2.DocDate,112) + Right('0000' + convert(nvarchar(4),A2.DocTime),4) between @FechaHoraInicio and @FechaHoraFin ),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 Y.FromWhsCod = 'MP' AND  Y.WhsCode = 'WIP' AND Convert(nvarchar(8),X.DocDate,112) + Right('0000' + convert(nvarchar(4),X.DocTime),4) between @FechaHoraInicio and @FechaHoraFin),0) as 'Cantidad Entregada',
A.OnHand as 'Inventario'
  
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 no se muy bien es como puedo hacer para que al momento de declarar mis variables de @FechaHoraInicio Y @FechaHoraFin varchar me pida los datos de fecha y tiempo o como puedo hacer para que me pida la cadena como tal que es lo que va a contener la fecha y la hora

Nelson1623 · 11 Mayo, 2016 23:57

No se si sea tan practico, pero podria armar cadenas y comparar el rango . Por favor pruebe este codigo .


/* 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 case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
  
  else 
  cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
  
  union all
  
  
  select case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
  
  else 
  cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end

Asanchez · 12 Mayo, 2016 21:52

ok, muchas gracias si me realiza la agrupación solo que no se como se estaría funcionando con mi código, la verdad soy muy nuevo en SQL y nuca he manejado variables ni concatenaciones

Nelson1623 · 12 Mayo, 2016 22:53

Disculpe este codigo esta mejor el anterior no controlaba las horas entre las 00:00 y las 00:59


/* 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 oi.docnum,oi.docdate,oi.doctime  from OINV oi
  where 
  
  (case when oi.DocTime<59 then   cast(convert(char(8),oi.docdate ,112)+'00'+substring(convert(char(2) ,oi.doctime ),1,3) as nvarchar(100))
  else
  
  
   case when oi.doctime >959 then cast(convert(char(8),oi.docdate ,112)+convert(char(4) ,oi.doctime ) as nvarchar(100))
  
  else 
  cast(convert(char(8),oi.docdate ,112)+'0'+substring(convert(char(3) ,oi.doctime ),1,3) as nvarchar(100))end
  end)
between 
(case when @HorIni <59 then  cast(convert(char(8),@fecini ,112)+'00'+substring(convert(char(2) ,@horini ),1,3) as nvarchar(100))
else

 case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
  
  else 
  cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
  end) 
  and 
  
  (case when @HorFin <59 then  cast(convert(char(8),@fecfin ,112)+'00'+substring(convert(char(2) ,@horFin ),1,3) as nvarchar(100))
  else
   case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
  
  else 
  cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end
  end)

Asanchez · 13 Mayo, 2016 14:38

Muchas gracias les dejo la 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(Y.Quantity)
> FROM OWTR X INNER JOIN WTR1 Y ON Y.DocEntry=X.DocEntry
> WHERE Y.ItemCode=T.ItemCode AND Y.FromWhsCod = 'MP' AND  Y.WhsCode = 'WIP' AND

> (case when X.DocTime<59 then   cast(convert(char(8),X.docdate ,112)+'00'+substring(convert(char(2) ,X.doctime ),1,3) as nvarchar(100))
>   else
>     
>    case when X.doctime >959 then cast(convert(char(8),X.docdate ,112)+convert(char(4) ,X.doctime ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),X.docdate ,112)+'0'+substring(convert(char(3) ,X.doctime ),1,3) as nvarchar(100))end
>   end)
> between 
> (case when @HorIni <59 then  cast(convert(char(8),@fecini ,112)+'00'+substring(convert(char(2) ,@horini ),1,3) as nvarchar(100))
> else

>  case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
>   end) 
>   and 
>   
>   (case when @HorFin <59 then  cast(convert(char(8),@fecfin ,112)+'00'+substring(convert(char(2) ,@horFin ),1,3) as nvarchar(100))
>   else
>    case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end
>   end)),0) as 'Historial Estock',

> 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.Canceled='N' AND
>   
>   (case when A1.DocTime<59 then   cast(convert(char(8),A1.docdate ,112)+'00'+substring(convert(char(2) ,A1.doctime ),1,3) as nvarchar(100))
>   else
>     
>    case when A1.doctime >959 then cast(convert(char(8),A1.docdate ,112)+convert(char(4) ,A1.doctime ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),A1.docdate ,112)+'0'+substring(convert(char(3) ,A1.doctime ),1,3) as nvarchar(100))end
>   end)
> between 
> (case when @HorIni <59 then  cast(convert(char(8),@fecini ,112)+'00'+substring(convert(char(2) ,@horini ),1,3) as nvarchar(100))
> else

>  case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
>   end) 
>   and 
>   
>   (case when @HorFin <59 then  cast(convert(char(8),@fecfin ,112)+'00'+substring(convert(char(2) ,@horFin ),1,3) as nvarchar(100))
>   else
>    case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end
>   end)),0) as 'Entradas de Mescancia',

>   IsNull((SELECT Sum(B.Quantity)
> FROM RPD1 B INNER JOIN ORPD A2 ON B.DocEntry = A2.DocEntry
> WHERE B.ItemCode=T.ItemCode AND A2.Canceled='N' AND   
>   (case when A2.DocTime<59 then   cast(convert(char(8),A2.docdate ,112)+'00'+substring(convert(char(2) ,A2.doctime ),1,3) as nvarchar(100))
>   else
>     
>    case when A2.doctime >959 then cast(convert(char(8),A2.docdate ,112)+convert(char(4) ,A2.doctime ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),A2.docdate ,112)+'0'+substring(convert(char(3) ,A2.doctime ),1,3) as nvarchar(100))end
>   end)
> between 
> (case when @HorIni <59 then  cast(convert(char(8),@fecini ,112)+'00'+substring(convert(char(2) ,@horini ),1,3) as nvarchar(100))
> else

>  case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
>   end) 
>   and 
>   
>   (case when @HorFin <59 then  cast(convert(char(8),@fecfin ,112)+'00'+substring(convert(char(2) ,@horFin ),1,3) as nvarchar(100))
>   else
>    case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end
>   end)),0) as 'Devoluciones',


>   ISNULL((SELECT SUM(Y.Quantity)
> FROM OWTR X INNER JOIN WTR1 Y ON Y.DocEntry=X.DocEntry
> WHERE Y.ItemCode=T.ItemCode AND Y.FromWhsCod = 'MP' AND  Y.WhsCode = 'WIP' AND

> (case when X.DocTime<59 then   cast(convert(char(8),X.docdate ,112)+'00'+substring(convert(char(2) ,X.doctime ),1,3) as nvarchar(100))
>   else
>     
>    case when X.doctime >959 then cast(convert(char(8),X.docdate ,112)+convert(char(4) ,X.doctime ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),X.docdate ,112)+'0'+substring(convert(char(3) ,X.doctime ),1,3) as nvarchar(100))end
>   end)
> between 
> (case when @HorIni <59 then  cast(convert(char(8),@fecini ,112)+'00'+substring(convert(char(2) ,@horini ),1,3) as nvarchar(100))
> else

>  case when @HorIni >959 then cast(convert(char(8),@fecini ,112)+convert(char(4) ,@HorIni ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecini ,112)+'0'+substring(convert(char(3) ,@HorIni ),1,3) as nvarchar(100))end
>   end) 
>   and 
>   
>   (case when @HorFin <59 then  cast(convert(char(8),@fecfin ,112)+'00'+substring(convert(char(2) ,@horFin ),1,3) as nvarchar(100))
>   else
>    case when @HorFin >959 then cast(convert(char(8),@fecFin ,112)+convert(char(4) ,@HorFin ) as nvarchar(100))
>   
>   else 
>   cast(convert(char(8),@fecFin ,112)+'0'+substring(convert(char(3) ,@HorFin ),1,3) as nvarchar(100))end
>   end)),0) as 'Cantidad Entregada',


> A.OnHand as 'Inventario'

>  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,T.U_TIPO_DE_VENTA
> ORDER BY T.ItemCode