Compare records of a table and filter their data in sql server

Question

I have a table including data like below:

PersonalID | Date

193        | 2017-06-01 08:02:00
193        | 2017-06-01 08:03:00
193        | 2017-06-01 08:03:00
193        | 2017-06-01 08:04:00
193        | 2017-06-01 08:09:00
193        | 2017-06-01 09:01:00
193        | 2017-06-01 09:06:00
193        | 2017-06-01 09:08:00

I want to select all records that their date difference is bigger than 10 minutes.

For example, according this data I want to show records with date ‘2017-06-01 08:02:00’ and ‘2017-06-01 09:01:00’ and ignore other records.

I can ignore duplicate records (with same date) by distinct keyword, but I don’t know how to compare records and select those with date difference bigger than 10 minutes.

I use this query to achieve this, but it returns wrong records.

declare @space int = 10;

with aaa as (select main.ID, main.PersonalID, main.Date 
from HZG_Traffic main
where exists(select * from HZG_Traffic tr
where tr.PersonalID = main.PersonalID and 
ABS(DATEDIFF(MI, main.Date,tr.Date)) < @space and 
ABS(DATEDIFF(MI, main.Date, tr.Date)) <> 0)
and main.PersonalID = 193)

Select * from aaa
where id not in 
(select 
    MIN(ID) 
from aaa
group by 
PersonalID,
DATEPART(DAY, Date), DATEPART(MONTH, Date), DATEPART(YEAR, Date), 
DATEPART(HOUR, Date))
order by Date desc

Can you help me or do you have better idea for this problem?

thanks

UPDATE:

Thanks for solutions, I'm using SQL Server 2014.

Answer 1

You say you want 2017-06-01 08:02:00.000 and 2017-06-01 09:01:00.000, if so, you can try something like this.

SELECT  * 
FROM    HZG_Traffic t1 
        CROSS APPLY (   
           SELECT MIN([Date]) AS MinDate 
           FROM   HZG_Traffic t2 
           WHERE  t1.PersonalID = t2.PersonalID 
                  AND DATEDIFF(Minute, t2.[Date], t1.[Date]) < 10) t2
WHERE   t1.[Date]   = t2.MinDate

SQL Fiddle Demo

Answer 2

It really depends on which version of SQL Server you are using. Here are two solutions, one that will work with SQL Server 2012 and higher, and one that will work with SQL Server 2008 and higher.

The first is for SQL 2008 and higher:

/* Populating the temp table with the data */
DECLARE @HZG_Traffic TABLE
(PersonalID INT,Date DATETIME);

INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:08:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:02:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:03:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:03:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:04:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:09:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:01:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:06:00');

/* Start with a CTE to number each record for the PersonalID */
WITH main AS (
    SELECT
        ROW_NUMBER() OVER(PARTITION BY ht.PersonalID ORDER BY ht.Date) AS Row_No
        ,ht.PersonalID
        ,ht.Date
    FROM @HZG_Traffic AS ht
    )
SELECT
    main.PersonalID
    ,main.Date
FROM main
/* Self-join to get the previous record */
LEFT JOIN main AS prev ON main.PersonalID = prev.PersonalID AND main.Row_No-1 = prev.Row_No
/* Another self join to get the next record */
LEFT JOIN main AS nex ON main.PersonalID = nex.PersonalID AND main.Row_No+1 = nex.Row_No
/* Have the OR clause so it will return both records */
WHERE 10 <= DATEDIFF(MINUTE, main.Date, nex.Date)
OR 10 <= DATEDIFF(MINUTE, prev.Date,main.Date);

This one will work with SQL 2012 and higher:

/* Populating the temp table with the data */
DECLARE @HZG_Traffic TABLE
(PersonalID INT,Date DATETIME);

INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:08:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:02:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:03:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:03:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:04:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 08:09:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:01:00');
INSERT INTO @HZG_Traffic (PersonalID,Date) VALUES (193,'2017-06-01 09:06:00');

/* Start with a CTE to get the next and previous records */
WITH main AS (
SELECT 
    ht.PersonalID, ht.Date
    ,LEAD(ht.Date) OVER(PARTITION BY ht.PersonalID ORDER BY ht.Date) AS Next_Date
    ,LAG(ht.Date) OVER(PARTITION BY ht.PersonalID ORDER BY ht.Date) AS Prev_Date
FROM @HZG_Traffic AS ht
)
SELECT 
    main.PersonalID
    ,main.Date
FROM main
/* Have the OR clause so it will return both records */
WHERE 10 <= DATEDIFF(MINUTE, main.Date, main.Next_Date)
OR 10 <= DATEDIFF(MINUTE, main.Prev_Date, main.Date);

Answer 3

Assuming that you are using SQL Server 2012 or later, you can use the LAG & LEAD functions...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    PersonalID INT NOT NULL,
    SomeDate DATETIME2(0) NOT NULL 
    );
INSERT #TestData (PersonalID, SomeDate) VALUES 
    (193, '2017-06-01 08:02:00'),
    (193, '2017-06-01 08:03:00'),
    (193, '2017-06-01 08:03:00'),
    (193, '2017-06-01 08:04:00'),
    (193, '2017-06-01 08:09:00'),
    (193, '2017-06-01 09:01:00'),
    (193, '2017-06-01 09:06:00'),
    (193, '2017-06-01 09:08:00');

-- SELECT * FROM #TestData td;

--==================================================

WITH 
    cte_LagLead AS (
        SELECT 
            td.PersonalID, td.SomeDate,
            LagMins = ABS(DATEDIFF(MINUTE, td.SomeDate, LAG(td.SomeDate, 1, td.SomeDate) OVER (PARTITION BY td.PersonalID ORDER BY td.SomeDate))),
            LeadMins = DATEDIFF(MINUTE, td.SomeDate,  LEAD(td.SomeDate, 1, td.SomeDate) OVER (PARTITION BY td.PersonalID ORDER BY td.SomeDate))
        FROM
            #TestData td
        )
SELECT 
    ll.PersonalID, ll.SomeDate
FROM
    cte_LagLead ll
WHERE 
    ll.LagMins > 10
    OR 
    ll.LeadMins > 10;

Results...

PersonalID  SomeDate
----------- ---------------------------
193         2017-06-01 08:09:00
193         2017-06-01 09:01:00