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
249 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 49

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 49

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 49

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 49

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 49

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 49

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 49

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

696 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