SQLTeam.com | Weblogs | Forums

Time difference query


#1

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.


#2

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


#3

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


#4

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


#5

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
;

#6

Thank you bitsmed. it really help.