Sql select distinct based on datetime range

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a data in my table like this

001800081 2020-05-19 11:59:04.720
001800081 2020-05-19 12:01:51.997
001800081 2020-05-19 22:03:58.627
001800081 2020-05-19 22:04:03.190

I want to sql query result like this
001800081 2020-05-19 12:01:51.997
001800081 2020-05-19 22:03:58.627

I want to select distinct data based on range time ,
for every data have 1 hour range example

first row has 2 minutes range with second row ,
i want to select 1 of them as the result ,
Second row has 10 hour range with third row ,so i want to select both ,
Third row has 2 minutes range with fourth row ,so i want to select 1 of them ,

I just want to select one data for every data have datetime range < 5 minutes

How can i solve this?

What I have tried:

this is my query ,

it's not complete yet

Select ROW_NUMBER() Over (Order by Timer_finger asc) as 'rownum' ,a.* from
T_Finger_Log a where CONVERT(Date,Insert_Date) = CONVERT(date,getdate())

Posted 18-May-20 22:09pm

Member 14760154

Updated 18-May-20 23:33pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-05-18T23:34:00

Take a look at:
LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^]
LAG (Transact-SQL) - SQL Server | Microsoft Docs[^]

Usage:

SQL

DECLARE @T_Finger_Log TABLE(Timer_finger VARCHAR(30), Insert_Date DATETIME)

INSERT INTO @T_Finger_Log (Timer_finger, Insert_Date)
VALUES('001800081', '2020-05-19 11:59:04.720'),
('001800081', '2020-05-19 12:01:51.997'),
('001800081', '2020-05-19 22:03:58.627'),
('001800081', '2020-05-19 22:04:03.190')


SELECT *
FROM
(
	SELECT *, DATEDIFF(N, Insert_Date, LEAD(Insert_Date) OVER(PARTITION BY Timer_finger ORDER BY Insert_Date)) MinutesPast 
	FROM @T_Finger_Log 
) T
WHERE T.MinutesPast<5

Result:

Timer_finger	Insert_Date	MinutesPast (to next 'row')
001800081	2020-05-19 11:59:04.720	2
001800081	2020-05-19 22:03:58.627	1