SQL Server - Consulta de datos por intervalo de tiempo

Val: 5

Consulta de datos por intervalo de tiempo

Publicado por Ricardo Alejandro (2 intervenciones) el 23/03/2021 18:31:10

Hola

Cuento con una problemática: tengo que recuperar registros que en intervalo de tiempo tengan un máximo de minutos de 4, y que sean 3 o mas operaciones, el mismo dia y el mismo centro de operacion. Los registros marcados con la flecha son los que deben de resultar.

Nucliente fhoperacion centro horaoperacion    NU_MOVIMIENTO        MONTO

--------- ----------- ------ ---------------- -------------------- --------------------

23394459  2020-03-18  6512   09:21:13.0000000 4831671              3980.00

23388753  2020-03-18  6512   09:21:41.0000000 4831696              4000.00

8215508   2020-03-18  6512   09:50:58.0000000 4833707              4000.00

23395729  2020-03-18  6512   09:59:13.0000000 4834287              4000.00

23394971  2020-03-18  6512   10:10:42.0000000 4835184              3800.00

23392108  2020-03-18  6512   10:35:38.0000000 4837279              4000.00

23396036  2020-03-18  6512   11:03:25.0000000 4839575              4000.00

23389732  2020-03-18  6512   11:12:43.0000000 4840415              4000.00

23392340  2020-03-18  6512   11:19:00.0000000 4841026              4000.00

23392739  2020-03-18  6512   11:51:04.0000000 4844012              4000.00

23392073  2020-03-18  6512   11:57:58.0000000 4844648              4000.00

23397239  2020-03-18  6512   12:05:19.0000000 4845305              4000.00

18474788  2020-03-18  6512   12:14:18.0000000 4846105              4000.00

23387478  2020-03-18  6512   12:26:23.0000000 4847305              4000.00

23389129  2020-03-18  6512   12:31:52.0000000 4847824              4000.00

23395747  2020-03-18  6512   12:35:17.0000000 4848117              4000.00

23389770  2020-03-18  6512   12:42:00.0000000 4848751              4000.00 <--------

23397045  2020-03-18  6512   12:45:13.0000000 4849053              4000.00 <--------

23395751  2020-03-18  6512   12:47:40.0000000 4849284              3900.00 <--------

23389430  2020-03-18  6512   12:49:17.0000000 4849448              4000.00 <--------

20330399  2020-03-18  6512   12:55:48.0000000 4850052              4000.00

12796485  2020-03-18  6512   13:02:42.0000000 4850625              4000.00

23397067  2020-03-18  6512   13:17:11.0000000 4851866              4000.00

23389442  2020-03-18  6512   13:23:51.0000000 4852439              4000.00

19773684  2020-03-18  6512   13:32:13.0000000 4853199              4000.00

Lo que intente fue hacer un join a la misma tabla, en el ON determinado por un DATEADD y que agregara 4 minutos pero me regresa todos los datos de mi tabla:

select

a.Nucliente,a.fhoperacion,a.centro,a.horaoperacion,a.NU_MOVIMIENTO,a.MONTO

from @RESTEMP a

left join @RESTEMP b on a.fhoperacion = b.fhoperacion and a.centro =b.centro and a.horaoperacion between b.horaoperacion and DATEADD(minute,4,CAST(b.horaoperacion AS TIME))

order by a.centro, a.fhoperacion, a.horaoperacion

También pensé en hacer un ciclo while para que recorra los datos pero no tengo idea de como hacerlo, todo esto es para pasarlo a un procedimiento almacenado.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 3.250

Consulta de datos por intervalo de tiempo

Publicado por Isaias (4558 intervenciones) el 23/03/2021 20:19:08

Otra observación, nos podrias enviar el script de creacion de tu tabla y el insert de los registros, nos ayuda para realizar las pruebas

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 142

Val: 5

Consulta de datos por intervalo de tiempo

Publicado por Ricardo Alejandro (2 intervenciones) el 23/03/2021 20:39:04

Claro que si, te redacto los datos:

DECLARE @RESTEMP AS TABLE (

Nucliente varchar(8),fhoperacion date,centro varchar(4),horaoperacion time,NU_MOVIMIENTO bigint,MONTO varchar(20)

INSERT INTO @RESTEMP values

('18474788','2020-03-18','6512','12:14:18.0000000','4846105','4000.00'),

('23387478','2020-03-18','6512','12:26:23.0000000','4847305','4000.00'),

('23389129','2020-03-18','6512','12:31:52.0000000','4847824','4000.00'),

('23395747','2020-03-18','6512','12:35:17.0000000','4848117','4000.00'),

('23389770','2020-03-18','6512','12:42:00.0000000','4848751','4000.00'),

('23397045','2020-03-18','6512','12:45:13.0000000','4849053','4000.00'),

('23395751','2020-03-18','6512','12:47:40.0000000','4849284','3900.00'),

('23389430','2020-03-18','6512','12:49:17.0000000','4849448','4000.00'),

('20330399','2020-03-18','6512','12:55:48.0000000','4850052','4000.00'),

('23427506','2020-03-21','6512','13:51:00.0000000','4986942','4000.00'),

('23431651','2020-03-21','6512','13:53:09.0000000','4987068','3800.00'),

('23419965','2020-03-21','6512','13:59:37.0000000','4987431','4000.00'),

('23429792','2020-03-21','6512','14:01:52.0000000','4987583','4000.00'),

('23430735','2020-03-21','6512','14:02:24.0000000','4987623','4000.00'),

('23419300','2020-03-21','6512','14:03:58.0000000','4987710','4000.00'),

('23428980','2020-03-21','6512','14:08:00.0000000','4987932','3900.00'),

('23432533','2020-03-21','6512','14:11:06.0000000','4988109','4000.00'),

('23418771','2020-03-21','6512','14:17:27.0000000','4988473','4000.00'),

('23426590','2020-03-21','6512','14:25:57.0000000','4989002','3990.00'),

('23424853','2020-03-21','6512','14:27:45.0000000','4989111','4000.00'),

('23427975','2020-03-21','6512','14:29:11.0000000','4989178','4000.00'),

('21917484','2020-03-21','6512','14:35:01.0000000','4989541','3900.00'),

('23431706','2020-03-21','6512','14:36:26.0000000','4989618','4000.00'),

('23431261','2020-03-21','6512','14:58:03.0000000','4990793','4000.00'),

('23420985','2020-03-21','6512','15:16:34.0000000','4991744','4000.00'),

('23424783','2020-03-21','6512','15:21:07.0000000','4991984','4000.00'),

('19669006','2020-03-21','6512','15:25:33.0000000','4992220','4000.00'),

('23425729','2020-03-21','6512','15:27:09.0000000','4992309','4000.00'),

('23424209','2020-03-21','6512','15:59:03.0000000','4993847','4000.00'),

('23423061','2020-03-21','6512','16:30:36.0000000','4995273','4000.00'),

('23419751','2020-03-21','6512','16:43:16.0000000','4995787','4000.00'),

('23423417','2020-03-21','6512','16:47:14.0000000','4995970','4000.00'),

('23425321','2020-03-21','6512','16:48:13.0000000','4996015','4000.00'),

('23420025','2020-03-21','6512','16:56:11.0000000','4996378','4000.00'),

('23421521','2020-03-21','6512','17:26:30.0000000','4997699','4000.00'),

('23422475','2020-03-21','6512','17:31:43.0000000','4997916','4000.00'),

('23425138','2020-03-21','6512','17:32:15.0000000','4997942','4000.00'),

('21937666','2020-03-21','6512','17:49:59.0000000','4998667','4000.00'),

('23425308','2020-03-21','6512','17:50:38.0000000','4998695','4000.00'),

('23419350','2020-03-21','6512','17:53:16.0000000','4998799','4000.00'),

('23419457','2020-03-21','6512','17:56:29.0000000','4998954','4000.00'),

('23431692','2020-03-21','6512','18:03:28.0000000','4999239','3860.00'),

('23420807','2020-03-21','6512','18:06:21.0000000','4999371','4000.00'),

('4440479','2020-03-21','6512','18:24:04.0000000','5000089','4000.00') ,

('23419425','2020-03-21','6512','18:33:41.0000000','5000496','4000.00'),

('23419176','2020-03-21','6512','19:10:04.0000000','5001938','4000.00'),

('23432527','2020-03-21','6512','19:12:10.0000000','5002021','3856.00'),

('23424831','2020-03-21','6512','19:13:40.0000000','5002075','4000.00'),

('23426864','2020-03-21','6512','19:22:09.0000000','5002364','4000.00'),

('23430523','2020-03-21','6512','19:28:37.0000000','5002579','4000.00'),

('23423876','2020-03-21','6512','19:33:28.0000000','5002740','4000.00'),

('23420825','2020-03-21','6512','19:33:53.0000000','5002754','3800.00'),

('9453335','2020-03-21','6512','19:44:41.0000000','5003102','4000.00') ,

('23419040','2020-03-21','6512','19:53:44.0000000','5003388','3980.00'),

('23419607','2020-03-21','6512','19:55:32.0000000','5003436','3800.00'),

('23428826','2020-03-21','6512','20:05:26.0000000','5003673','4000.00'),

('23420832','2020-03-21','6512','20:06:24.0000000','5003691','4000.00'),

('23432465','2020-03-21','7113','13:55:29.0000000','4987197','4000.00'),

('23424979','2020-03-21','7113','17:18:27.0000000','4997346','3980.00'),

('17276208','2020-03-30','2047','10:29:42.0000000','5234369','4000.00'),

('6126109','2020-03-30','2047','10:36:15.0000000','5234706','4000.00') ,

('23486694','2020-03-30','2047','10:43:29.0000000','5235080','4000.00'),

('9530896','2020-03-30','2047','10:51:55.0000000','5235520','4000.00') ,

('14472647','2020-03-30','2047','11:09:00.0000000','5236434','4000.00'),

('23490039','2020-03-30','2047','11:13:04.0000000','5236648','4000.00'),

('12575942','2020-03-30','2047','11:42:14.0000000','5238295','4000.00'),

('19641739','2020-03-30','2047','11:46:56.0000000','5238573','4000.00'),

('23488265','2020-03-30','2047','12:21:43.0000000','5240663','4000.00'),

('23483934','2020-03-30','2047','12:30:32.0000000','5241222','4000.00'),

('22798835','2020-03-30','2047','13:26:00.0000000','5244514','3840.00'),

('23483253','2020-03-30','2047','13:31:54.0000000','5244861','4000.00'),

('23484026','2020-03-30','2047','13:33:28.0000000','5244949','4000.00'),

('23488750','2020-03-30','2047','13:38:27.0000000','5245247','4000.00'),

('23485689','2020-03-30','2047','13:47:55.0000000','5245772','3990.00'),

('22851705','2020-03-30','2047','14:01:22.0000000','5246522','4000.00'),

('18817383','2020-03-30','2047','14:03:45.0000000','5246664','4000.00'),

('23487130','2020-03-30','2047','14:04:47.0000000','5246707','4000.00'),

('13635992','2020-03-30','2047','14:09:52.0000000','5246997','3650.00'),

('23485334','2020-03-30','2047','14:11:26.0000000','5247073','4000.00'),

('23483855','2020-03-30','2047','14:22:04.0000000','5247680','4000.00'),

('23485147','2020-03-30','2047','14:41:38.0000000','5248652','4000.00'),

('23489149','2020-03-30','2047','14:53:23.0000000','5249276','4000.00'),

('23486562','2020-03-30','2047','15:03:22.0000000','5249728','4000.00'),

('20260509','2020-03-30','2047','15:12:30.0000000','5250120','4000.00'),

('23487355','2020-03-30','2047','15:16:36.0000000','5250284','4000.00'),

('23489539','2020-03-30','2047','15:25:21.0000000','5250663','4000.00'),

('23486348','2020-03-30','2047','15:55:11.0000000','5251929','4000.00'),

('23486513','2020-03-30','2047','16:07:54.0000000','5252438','4000.00')

Valora esta respuesta

Comentar