SQL Server - Calcular direfencia campo Hora entre registros

Estimados

Necesito de su ayuda, tengo una tabla donde tengo los campos

RUT FECHA HORA MONTO
111 20110102 112041 3000
111 20110102 112240 5000
123 20110103 153055 1000
444 20110103 163015 2500
444 20110103 163120 3000
444 20110103 164010 4000

Mi consulta es, ¿como puedo calcular la diferencia en minutos entre registros pertenecientes a un mismo RUT y fecha?.

por ejemplo

resultado para RUT 111

RUT FECHA TIEMPO (min)
111 20110102 2

por favor,

saludos y gracias.

Kinetic_Brain,

Según el ejemplo que has dado, he hecho el siguiente SQL, pero no estoy seguro si solucionará todos tus problemas. Analízalo:

DECLARE @RUT smallint
DECLARE @fecha datetime

SET @RUT=111
SET @fecha= '2011-01-02'

DECLARE @tablahoras TABLE(
id_tabla INT IDENTITY(1,1) NOT NULL,
hora DATETIME
)

INSERT INTO @tablahoras (hora)
SELECT CONVERT(VARCHAR(8),hora,108)
FROM diferencia_horas
WHERE RUT=@RUT AND fecha=@fecha

SELECT @RUT,@fecha,DATEPART(Minute,t2.hora)-DATEPART(Minute,t1.hora)
FROM (SELECT hora FROM @tablahoras WHERE id_tabla=1) t1,
(SELECT hora FROM @tablahoras WHERE id_tabla=2) t2

Saludos.

Loría.

Gracias Loría

la idea era no setear un valor para la variable RUT, sino que con una query me entregara todos los RUT que agrupados por RUT y fecha me entregara un promedio de minutos pertenecientes a un dia (fecha), pero es un promedio de la diferencia de minutos entre un registro y el siguiente.

no se si me explico.

lo que sedeo saber es que un rut en promedio deberia debemorar de 6 a 15 minutos en registrar una transaccion, entonces poder determinar los registros ingresados por tal RUT en Tal fecha y la diferencia entre cada registro.

Hola kinetic_brain:

Tengo una duda con los datos que pones de ejemplo y la salida que debe presentar, ya que sólo pones el resultado del RUT 111, pero no mencionas lo que debe mostrar para los casos 123 y 444.

Por ejemplo, el caso 123 sólo tiene un registro, por lo que puedo suponer que la diferencia de tiempo debería de ser 0 (pues es único). El problema viene con el ejemplo 444, aquí manejas 3 registros, con las siguientes horas (16:30:15, 16:31:20, 16:40:10) Aquí mi pregunta es si el resultado debería ser un solo registro donde se muestre la diferencia entre la primer hora y la última o dos registros mostrando la diferencia entre cada par:

RUT|TIEMPO
444|10

o

RUT|TIEMPO
444|1
444|9

Tampoco mencionas de qué tipo son tus datos, lo que acarrea problemas. Para el ejemplo que voy a proponerte estoy suponiendo que la fecha y la hora están almacenadas en un mismo campo tipo DATETIME, si no es así, pues es cuestión de que nos indiques qué tipo de datos es cada uno para hacer las conversiones pertinentes, también voy a considerar que la información debe mostrar un solo registro por cada RUT, mistrando la diferencia en minutos entre la menor fecha y la mayor:

declare @TuTabla table (RUT int, FECHA_HORA datetime, MONTO int);
insert into @TuTabla values (111, '2011-01-02 11:20:41', 3000)
insert into @TuTabla values (111, '2011-01-02 11:22:40', 5000)
insert into @TuTabla values (123, '2011-01-03 15:30:55', 1000)
insert into @TuTabla values (444, '2011-01-03 16:30:15', 2500)
insert into @TuTabla values (444, '2011-01-03 16:31:20', 3000)
insert into @TuTabla values (444, '2011-01-03 16:40:10', 4000)

select
RUT,
Convert(varchar, FECHA_HORA, 103) as FECHA,
datediff(minute, min(FECHA_HORA), max(FECHA_HORA)) as TIEMPO
from @TuTabla group by RUT, Convert(varchar, FECHA_HORA, 103);

RUT|FECHA|TIEMPO
111|02/01/2011|2
123|03/01/2011|0
444|03/01/2011|10

Espero que esto sea más o menos lo que necesitas, si no es así, comenta las observaciones que te hice arriba y trata de aclarar mejor la salida que necesitas.

Saludos
Leo.

Gracias Leonardo

si es algo parecido y claro no me explique muy bien. todos son campos de texto y fecha es un campo independiente de hora.

RUT FECHA HORA MONTO N_RECLAMO
111 20110102 112041 3000 2233
111 20110102 112240 5000 2234
123 20110103 153055 1000 2235
444 20110103 163015 2500 2236
444 20110103 163120 3000 2237
444 20110103 164010 4000 2238

Lo que necesito y no se como, es como puedo obtener para un RUT determinado y decha determina la direcencia entre cada hora.

Como me mencionas deberia ser algo como diferencia entre cada par (hora), obviamente deberia estar ordenada por fecha y hora la tabla antes y para que mostrara solo un resultado en minutos por RUT, fecha y deberia mostar el promedio de las diferencias.

Agrego un campo mas N_RECLAMO que seria unico por registro esto me serviria para contar la cantidad de reclamos por RUT.

deberia quedar algo asi:

RUT FECHA PROM_HORA SUM_MONTO CANT_N_RECLAMO
111 20110102 2 8000 2
123 20110103 0 1000 1
444 20110103 5 9500 3


para este caso podria dejar fuera todos los que tienen CANT_N_RECLAMOS 1

El tema es que es raro que un rut (ejecutivo) ingrese transacciones entre una y otra en menos de 10 min promedio .

Por favor si sabes como llegar a eso.

Veamos si entendí bien, para el caso del RUT 444 hay dos pares de registros a considerar, uno entre las 16:30:15 y las 16:31:20 (lo que implica un intervalo de 1 minuto) y el otro entre las 16:31:20 y las 16:40:10 es decir (lo que implica un intervalo de 9 minutos)

Según yo, lo que haces es sumar 9+1 y lo divides entre 2 correcto?

Mira, no me gusta simplemente dar la respuesta a los problemas de los foristas, por lo que me gustaría que hicieras un intento por hacer tu mismo la consulta. Te explico cuál fue la lógica que seguí para obtener lo que estás buscando.

En primer lugar, las comparaciones entre cadenas no te sirven para nada, por lo que es necesario que conviertas tu campo HORA de varchar a datetime. Esto lo puedes hacer así:

convert(datetime, substring(hora, 1, 2) + ':' + substring(hora, 3, 2) + ':' + substring(hora, 5, 2), 108)

En realidad creo que esto sería lo más complicado, la lógica que seguí para obtener lo que quieres es la siguiente, primero la idea es obtener para cada registro EL INMEDIATO POSTERIOR, es decir aquel registro que tenga el mismo RUT, la misma fecha pero la HORA > que la hora del registro seleccionado. Esto lo puedes obtener con una SUBCONSULTA mas o menos así:

PARA RUT 111 --> HORA 11:20:41

Obtener EL PRIMER (TOP 1) registro de la tabla donde la fecha sea MAYOR a 11:20:41, tenga el mismo RUT y la misma FECHA

Convierte esto a SQL y listo. Con esto obtienes una tabla como esta:

RUT|FECHA|HORA|MONTO|SIGUIENTE_HORA
111|20110102|112041|3000|112240
111|20110102|112240|5000|NULL
123|20110103|153055|1000|NULL
444|20110103|163015|2500|163120
444|20110103|163120|3000|164010
444|20110103|164010|4000|NULL

Si observas algunos datos son NULL, esto indica que ya no hay registros con fecha posterior a la seleccionada.

Ahora bien, con los campos HORA y SIGUIENTE_HORA (ya convertidos como datetime) simplemente aplicas la función DATEDIFF(minute, HORA, SIGUIENTE_HORA) para obtener los minutos entre cada par, de tal manera que vas a obtener una tabla como esta:

RUT|FECHA|MONTO|DIFERENCIA_HORA
111|20110102|3000|2
111|20110102|5000|0
123|20110103|1000|0
444|20110103|2500|1
444|20110103|3000|9
444|20110103|4000|0

Con esto sólo aplicas las funciones de agrupación AVG para obtener el promedio de minutos entre las transacciones, COUNT(*) para obtener el número de reclamos y SUM(MONTO) para obtener la sumatoria de los montos para cada RUT.

Suena complicado, pero te aseguro que no lo es tanto. Haz el intento por obtener lo que aquí te explico, si tienes problemas pon algo del código que intentaste hacer y te ayudo a afinar lo que falta.

Saludos
Leo.

Gracias Leonardo, te pasaste

eso es lo que necesito, voy a seguir tus instrucciones y te cuento como me va.

muchas gracias.

Leonardo

a ver si me ayudas estoy con la primera parte de la consulta me da esto:

me esta repitiendo la hora

La fecha es solo para que no se demore en buscar.

SELECT A.RUT,A.FREC
,convert(datetime, substring(A.HORA, 1, 2) + ':' + substring(A.HORA, 3, 2) + ':' + substring(A.HORA, 5, 2), 108) AS HORA
,B.HORA AS HORA1
,SUM(MONTO) MONTO
FROM RCL A,
(SELECT DISTINCT /*TOP 1 */RUT,FREC,MAX(convert(datetime, substring(HORA, 1, 2) + ':' + substring(HORA, 3, 2) + ':' + substring(HORA, 5, 2), 108))HORA
,sum(MONTO) MONTO
FROM RCL
WHERE FREC > '20110208'
GROUP BY RUT,FREC) B
WHERE A.FREC > '20110208'
AND A.RUT=B.RUT
AND A.FREC=B.FREC
GROUP BY A.RUT,A.FREC,A.HORA,B.HORA

RESULTADO:


rut fecha hora hora2 monto
A401 20110209 1900-01-01 10:44:25.000 1900-01-01 10:44:25.000 2333
A462 20110209 1900-01-01 17:52:20.000 1900-01-01 17:52:20.000 3335
A467 20110209 1900-01-01 10:16:30.000 1900-01-01 16:32:03.000 3469
A467 20110209 1900-01-01 11:41:39.000 1900-01-01 16:32:03.000 8334
A467 20110209 1900-01-01 12:10:51.000 1900-01-01 16:32:03.000 20394
A467 20110209 1900-01-01 13:24:19.000 1900-01-01 16:32:03.000 3642
A467 20110209 1900-01-01 14:28:37.000 1900-01-01 16:32:03.000 2870
A467 20110209 1900-01-01 16:31:06.000 1900-01-01 16:32:03.000 2998
A369 20110209 1900-01-01 11:38:46.000 1900-01-01 11:39:51.000 11602
A369 20110209 1900-01-01 11:39:51.000 1900-01-01 11:39:51.000 2417

Hola de nuevo kinetic_brain:

El motivo por el que te está repitiendo la hora es porque estás seleccionando el MAX, como te comentaba lo que tienes que hacer es obtener el TOP 1 de los registros con una hora mayor a la que estás analizando, más o menos así:

declare @RCL table (rut varchar(5), fecha varchar(8), hora varchar(6), monto int)
insert into @RCL values ('A401', '20110209', '104425', 2333)
insert into @RCL values ('A462', '20110209', '175220', 3335)
insert into @RCL values ('A467', '20110209', '163106', 2998)
insert into @RCL values ('A467', '20110209', '101630', 3469)
insert into @RCL values ('A467', '20110209', '114139', 8334)
insert into @RCL values ('A467', '20110209', '121051', 20394)
insert into @RCL values ('A467', '20110209', '132419', 3642)
insert into @RCL values ('A467', '20110209', '142837', 2870)
insert into @RCL values ('A369', '20110209', '113846', 11602)
insert into @RCL values ('A369', '20110209', '113951', 2417)

SELECT
A.RUT,
A.fecha,
convert(datetime, substring(A.HORA, 1, 2) + ':' + substring(A.HORA, 3, 2) + ':' + substring(A.HORA, 5, 2), 108) AS HORA,
(
select top 1
convert(datetime, substring(B.HORA, 1, 2) + ':' + substring(B.HORA, 3, 2) + ':' + substring(B.HORA, 5, 2), 108)
from @RCL B where A.RUT = B.RUT and A.fecha = B.fecha and
convert(datetime, substring(B.HORA, 1, 2) + ':' + substring(B.HORA, 3, 2) + ':' + substring(B.HORA, 5, 2), 108) >
convert(datetime, substring(A.HORA, 1, 2) + ':' + substring(A.HORA, 3, 2) + ':' + substring(A.HORA, 5, 2), 108)
order by
convert(datetime, substring(B.HORA, 1, 2) + ':' + substring(B.HORA, 3, 2) + ':' + substring(B.HORA, 5, 2), 108)
) as HORA1,
monto
FROM @RCL A
order by RUT,
convert(datetime, substring(A.HORA, 1, 2) + ':' + substring(A.HORA, 3, 2) + ':' + substring(A.HORA, 5, 2), 108)

Con esto obtienes lo siguiente:

A369|20110209|1900-01-01 11:38:46.000|1900-01-01 11:39:51.000|11602
A369|20110209|1900-01-01 11:39:51.000|NULL|2417
A401|20110209|1900-01-01 10:44:25.000|NULL|2333
A462|20110209|1900-01-01 17:52:20.000|NULL|3335
A467|20110209|1900-01-01 10:16:30.000|1900-01-01 11:41:39.000|3469
A467|20110209|1900-01-01 11:41:39.000|1900-01-01 12:10:51.000|8334
A467|20110209|1900-01-01 12:10:51.000|1900-01-01 13:24:19.000|20394
A467|20110209|1900-01-01 13:24:19.000|1900-01-01 14:28:37.000|3642
A467|20110209|1900-01-01 14:28:37.000|1900-01-01 16:31:06.000|2870
A467|20110209|1900-01-01 16:31:06.000|NULL|2998

Como recordarás los registros con NULL marcan que ya no existen registros posteriores a la hora. Dale un vistazo a la consulta y si hay algo que no entiendas lo comentas.

Saludos.
Leo