?
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
Medium Priority
?
250 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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…
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 …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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