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.