Solved

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

Posted on 2013-11-08
12
248 Views
Last Modified: 2013-11-12
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
Comment
Question by:chaitu chaitu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39633081
are you only interested in those which have multiple records in the last minute?

what does your expected result look like?
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39633093
YES.

expected results are employee ids:1234,789
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39633097
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39633156
what is -4?

i want to retrieve those records which are inserted in the last 1 minute?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39633168
sorry, change to the number wanted

(late at night dyslexia: "4 times in last minute" became -4)
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39633176
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39633191
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39633197
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
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39633205
if i modify less than operator then records are coming .

 thedate_field <= dateadd( minute,-1,getdate() )
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39633207
am confusedhere.is it greater than or lesss than in this case?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39634950
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39634965
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question