• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

need to find out the records which are inserted for the last minute for the same employee id

1234 2013-11-07 10:34:01.507
1234 2013-11-07 10:34:02.500
1234 2013-11-07 10:34:02.430
1234 2013-11-07 10:34:11.490

3456 2013-11-07 10:34:01.507
3456 2013-11-07 10:36:01.507

789 2013-11-07 11:11:01.507
789 2013-11-07 11:11:11.507


need to find out the records which are inserted for the last minute for the same employee id?for example for employee:1234 the record inserted 4 times  in the last 1 minute.same in the case of 789 as well.
0
chaitu chaitu
Asked:
chaitu chaitu
  • 7
  • 5
1 Solution
 
PortletPaulCommented:
are you only interested in those which have multiple records in the last minute?

what does your expected result look like?
0
 
chaitu chaituAuthor Commented:
YES.

expected results are employee ids:1234,789
0
 
PortletPaulCommented:
try this
select
     employee_id
from YourTable
where thedate_field >= dateadd( minute,-4,getdate() )
group by
     employee_id
having count(*) > 1

Open in new window

{+edit} correction, should be >=
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
chaitu chaituAuthor Commented:
what is -4?

i want to retrieve those records which are inserted in the last 1 minute?
0
 
PortletPaulCommented:
sorry, change to the number wanted

(late at night dyslexia: "4 times in last minute" became -4)
0
 
chaitu chaituAuthor Commented:
employee ids are not coming from above query that are inserted in the last 1 minute.

select
     employee_id
from YourTable
where thedate_field >= dateadd( minute,-1,getdate() )
group by
     employee_id
having count(*) > 1
0
 
PortletPaulCommented:
Sorry I don't understand your last comment, is it to do with this?

As you didn't give table or field names anything I propose for those is an approximation.

[employee_id]
[thedate_field]
and
[YourTable]

All of these need to be replaced with the real names
0
 
PortletPaulCommented:
maybe this...

if you don't believe the original query is working try this:

select
     employee_id
, row_number() over partition by employee_id order by thedate_field DESC) as rn
from YourTable
where thedate_field >= dateadd( minute,-1,getdate() )

once you have corrected this for the actual names, run this query and see if an values in the rn column are greater than 1

You could also try this:

select *
from (

select
     employee_id
, row_number() over partition by employee_id order by thedate_field DESC) as rn
from YourTable
where thedate_field >= dateadd( minute,-1,getdate() )

   ) as d
where rn > 1
0
 
chaitu chaituAuthor Commented:
if i modify less than operator then records are coming .

 thedate_field <= dateadd( minute,-1,getdate() )
0
 
chaitu chaituAuthor Commented:
am confusedhere.is it greater than or lesss than in this case?
0
 
PortletPaulCommented:
It should be >=

Let's assume the current time is 09:17:05+02700
Which means you are interested in records inserted "AT or AFTER" 09:16:05+02700

for records inserted "at or after" you would use >=

---------------
I think the problem is that one minute may be too difficult for testing right now. It is very possible that if you execute the query you will get a null result because time is moving constantly

Just for testing, perhaps use 90 minutes

select
     employee_id
from YourTable
where thedate_field >= dateadd( minute,-90,getdate() )
group by
     employee_id
having count(*) > 1

Hopefully this will start to display records, then reduce the period progressively, say to 30 minutes and if that is returning records then try 10 minutes etc.

Oh, of course if 90 minutes returns no records try an even longer period.

----
If I understand your question correctly the query I provided will do exactly what you asked for:
locate records inserted in the last minute
& list only those employee_id's that have more than one record
0
 
PortletPaulCommented:
here's another approach, for testing, temporarily ignore this part of your request:
"only those employee_id's that have more than one record"

select
     employee_id
     , count(*) as count_of
from YourTable
where thedate_field >= dateadd( minute,-1,getdate() )
group by
     employee_id
/* having count(*) > 1 */
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now