SQLTeam.com | Weblogs | Forums

Time difference query

Hi there,

I have millions of records for one day and I need to find out which employee makes entries in a table with machine behavior. I have a colum for employee name, date time, catagory and amount column. I have 47 + entries in a second but all coming from different employees. I need to group on employee who made entries with difference of few seconds.
Please help, thanks in advance.

Would you please post your table schema (as CREATE TABLE ...), some sample input data, some expected results, and the query you have worked up so fa

in this sample data transactions of employee Alan were performed in a difference of one second time difference and two last transactions of john were performed with the difference of one second. I like a select statement which brings me all transaction made by one employee with the interval between 5 seconds.

emp_name datetime amount
Alan 2016-01-20 12:23:15 50
Alan 2016-01-20 12:23:16 50
Alan 2016-01-20 12:23:17 50
Alan 2016-01-20 12:23:18 50
Alan 2016-01-20 12:23:19 100
Alan 2016-01-20 13:00:20 50
Fisher 2016-01-20 18:00:41 40
Fisher 2016-01-20 11:28:12 30
Fisher 2016-01-20 09:00:00 50
Fisher 2016-01-20 10:00:44 70
John 2016-01-20 17:00:45 100
John 2016-01-20 06:00:00 233
John 2016-01-20 05:00:47 214
John 2016-01-20 18:00:48 20
John 2016-01-20 18:00:49 10

Hello, you can try this.
This is not very elaborate but brief .

SELECT
emp_name
,min(DateTimeAmount) as intervalStart
,MAX(DateTimeAmount) as IntervalEnd
,COUNT(emp_name) as x
FROM FAB_HistorialDeEstados with(nolock)
where datetimeamount between 'xxxxxxx' and 'xxxxxxxx'
group by emp_name
, year(DateTimeAmount)
,month(DateTimeAmount)
,day(DateTimeAmount)
,datepart(hh,DateTimeAmount)
,datepart(mi,DateTimeAmount)
,substring(cast(DATEPART(ss,DateTimeAmount) as varchar(2)),1,1)
,replace(replace(replace(replace(replace(replace(replace(replace(replace(substring(cast(DATEPART(ss,DateTimeAmount) as nvarchar(2)),2,1),'1','0'),'2','0'),'3','0'),'4','0'),'5','1'),'6','1'),'7','1'),'8','1'),'9','1')
--having (count(emp_name) > 1

I'm not 100% sure, what exactly it is, you're seeking.
If you want to show the "violating" records, do this:

select emp_name
      ,[datetime]
      ,amount
  from yourtable as a
 where exists (select 1
                 from yourtable as b
                where b.emp_name=a.emp_name
                  and abs(datediff(second,b.[datetime],a.[datetime]))<=5
                having count(*)>1
              )
;

If you want to show all record from an employee who has "violated" your rule, do this:

with cte
  as (select emp_name
        from yourtable as a
       where exists (select 1
                       from yourtable as b
                      where b.emp_name=a.emp_name
                        and abs(datediff(second,b.[datetime],a.[datetime]))<=5
                      having count(*)>1
                    )
       group by emp_name
     )
select b.emp_name
      ,b.[datetime]
      ,b.amount
  from cte as a
       inner join yourtable as b
               on b.emp_name=a.emp_name
;

If you want to show all record from an employee who has "violated" your rule, but only the days he/she "violated" your rule, do this:

with cte
  as (select emp_name
            ,dateadd(day,0,datediff(day,0,[datetime])) as from_date
            ,dateadd(day,1,datediff(day,0,[datetime])) as to_date
        from yourtable as a
       where exists (select 1
                       from yourtable as b
                      where b.emp_name=a.emp_name
                        and abs(datediff(second,b.[datetime],a.[datetime]))<=5
                      having count(*)>1
                    )
       group by emp_name
               ,datediff(day,0,[datetime])
     )
select b.emp_name
      ,b.[datetime]
      ,b.amount
  from cte as a
       inner join yourtable as b
               on b.emp_name=a.emp_name
              and b.[datetime]>=a.from_date
              and b.[datetime]<a.to_date
;

Thank you bitsmed. it really help.