Group by range

[cjkenworthy]

We have records in the following manner:

Record CreatedBy TimeCreated
1 Chris 13:30
2 Chris 12:12
3 Chris 13:31
4 Chris 12:14
5 Chris 09:34

I want to run a query that will fetch all records for a user where the TimeCreated is within 1 minute of another record, so for example this would retrieve:

Record CreatedBy TimeCreated
2 Chris 12:12
4 Chris 12:14
1 Chris 13:30
3 Chris 13:31

How can I do this row by row comparison - an ORDER BY' query would work, but it there are too may rows.

 

[NoCoolHandle]

I think the answer is you need a co-related subquery

e.g.

use crap
go
drop table a
go
create table  a
(Record int,createdby varchar(300),timecreated smalldatetime)
go
insert into a 
select 1,'Chris','1-1-1 13:30' union
select 2,'Chris','1-1-1 12:12' union
select 3,'Chris','1-1-1 13:31' union
select 4,'Chris','1-1-1 12:41' union
select 5,'Chris','1-1-1 12:14' union
select 6,'Chris','1-1-1 9:34' union
select 7,'Chris','1-1-1 13:32'

go

select * from a  a1 where Exists  (select *  from a a2  
where datediff(n,a1.timecreated,a2.timecreated) <=1
and a2.record <>  a1.record) order by 3

output =

Record createdby timecreated
----------- ---------- --------------------
2 Chris 2001-01-01 12:12:00
5 Chris 2001-01-01 12:14:00
4 Chris 2001-01-01 12:41:00
1 Chris 2001-01-01 13:30:00
3 Chris 2001-01-01 13:31:00
7 Chris 2001-01-01 13:32:00

 

[NoCoolHandle]

sorry try this one, I realized that the last one was bringing in 12:41 (probably beacuse the difference was less than some other (ie a negative value)

this should work better

select * from a  a1 where Exists  (select *  from a a2  
where (datediff(n,a1.timecreated,a2.timecreated) between -2 and 2)
and a2.record <>  a1.record)  order by 3

 

[rac2]

Each row has none or one mate, a row created within the next minute. So that could be the basis for a self join.

It is a little more complicated, in that simultaneous and multiple same-minute rows must be handled. Maybe the times are system times and extend to milliseconds and there are actually no simultaneous rows, at least not within the same millisecond.

SELECT a.Record, a.CreatedBy, a.TimeCreated
FROM myTable a
JOIN myTable b ON 
   a.CreatedBy = b.CreatedBy
   AND DATEDIFF(minute, a.TimeCreated, b.TimeCreated) < 1
ORDER BY a.CreatedBy, a.TimeCreated

So for a given row, is there another row created within 1 minute? If so, we have a row to join; if not the given row is discarded.

If Chris can create rows faster than one per minute, this would yield multiple rows for each given row. Which might be OK.

 

[cjkenworthy]

Thanks for all your help, The records only go into seconds, not milliseconds, and aren't generated al that quickly (about 10 a minute).

Chris.