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.
romsomIT DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
romsomIT DeveloperAuthor 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
Mark WillsTopic 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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

romsomIT DeveloperAuthor 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
romsomIT DeveloperAuthor Commented:
When I try the first part to visualize the dat I get an error message:
Incorrect syntax near '>'.
0
Mark WillsTopic 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romsomIT DeveloperAuthor Commented:
Thank you so much, this is exactly what I needed!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.