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
245 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
combining condition to rerun if failure within BEGIN..END 27 87
Stored Procedure 2 47
Best Query To Insert One Year Calendar 6 45
Generate Weekly Schedule 15 17
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now