SQL query - delete multiple records from time frame

Hi Experts,
I'm recording the time of the page visit by Emp_Id in SQL Server database.
My Visitors table contains the Emp_Id column (varchar) and the Last_Visited column, which is of datetime data type.
The primary key is on the ID column.
When an employee visits the page more than once in an hour, I would like to delete the new visits, and keep only the first one.
How can I write a query that takes care of this?
My pseudo code:
Delete multiple Emp_Id from Visitors where Last_Visited is less than 1 hour and keep the first Emp_Id only from that time frame.

I would appreciate your help.
romsomAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Sorry about that - would have been the IIF() function - only available from SQL 2012

So, if only select.... Try
;with delvisit as
(
  select id, emp_id, last_visited
         ,row_number() over (partition by emp_id, dateadd(d,0,datediff(d,0,last_visited)),datepart(hour,last_visited) order by last_visited) rn 
  from visitors
) select id, emp_id, last_visited
  from delvisit
  where RN = 1

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
I would be inclined to use row_number() function and delete rows that dont comply...

any test cases you wish to share ?
0
 
romsomAuthor Commented:
I'm looking at my database table now and I see records like this in the Emp_Id and Last_Visited columns:

Emp123           2018-03-01 10:57:48.000
Emp123           2018-03-01 10:56:35.000
Emp123           2018-03-01 10:56:30.000
Emp123           2018-03-01 10:55:26.000
Emp123           2018-03-01 10:54:50.000

I would like to keep only the record at the bottom, as that was the first one during this hour.
If Emp123 visited the page more than an hour ago, that record should stay.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
OK, so let's see if this might reveal from your visitors table.

First we will try to visualise the data
;with delvisit as
(
  select id, emp_id, last_visited
         ,row_number() over (partition by emp_id, dateadd(d,0,datediff(d,0,last_visited)),datepart(hour,last_visited) order by last_visited) rn 
  from visitors
) select id, emp_id, last_visited, rn, iif(RN > 1, 'Delete','Keep') as [Action]
  from delvisit
  order by emp_ID, last_visited, rn

Open in new window

Now, when we are very satisfied with the results, then we can move on to deleting. But check, double check and backup before the purge.
;with delvisit as
(
  select id, emp_id, last_visited
         ,row_number() over (partition by emp_id, dateadd(d,0,datediff(d,0,last_visited)),datepart(hour,last_visited) order by last_visited) rn 
  from visitors
) delete delvisit
  where RN > 1

Open in new window

P.S. I do use ID as a separate identity - wasnt exactly sure of your comment "The primary key is on the ID column."
0
 
romsomAuthor Commented:
Deleting is not necessary if I can just hide the multiple visits during the timeframe. This is my select statement:

SELECT [ID]
      ,[Emp_Id]
      ,[Last_Visited]
  FROM [MyDatabase].[dbo].[Visitors] order by Last_Visited desc
0
 
romsomAuthor Commented:
When I try the first part to visualize the dat I get an error message:
Incorrect syntax near '>'.
0
 
romsomAuthor Commented:
Thank you so much, this is exactly what I needed!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.