2

So I have data that is structured like this:

|ID | Reference| Name | Datetime |
 1       abc     John      2016-02-08 08:25:35
 2       abc     John      2016-02-08 08:25:37
 3       bnm     Eliza     2016-02-08 08:25:39
 4       abc     John      2016-02-08 08:25:42
.......
 351     cdf     Eliza     2016-06-01 15:25:42
 352     abc     John      2016-06-02 15:25:42

Due to an error in a logging system, which for the time being is outside my reach, I get several rows of data for what should just be one. I can have up to 20-30 duplicates within a 20 second timeframe. I want to select only one record of those duplicates where the time only differs by 20 seconds, but at the same time I want to include records, such as id 352, which would be excluded if I used a group by clause. I also have other records inside the that 20 second timeframe that I want to include, such as id 3, so I cant directly filter out records that are too close.

Is an option to make a kind of clustered column that combine Reference, name and datetime down to f.ex 20 second precision, and group by that?

I want to keep the duplicate with the earliest starttime.

  • Can you clarify which duplicates should be kept? For example, suppose you had duplicates with datetimes of 00:00:00, 00:00:15, 00:00:30, 00:00:45, and 00:01:00. What should be kept? – Joe Obbish Feb 22 '17 at 2:10
  • Thank you for your comment. I want to keep the duplicate with the earliest datetime. – B.Doe12 Feb 23 '17 at 13:17
1

You can achieve this goal by using the windowing functionlag and self joining the table onto itself. I've included an example below. You may need to tweak some of the code to get exactly what you want, but I think this comes really close.

The Setup

DECLARE @TestData AS TABLE
    (
    ID INT NOT NULL
    , Reference CHAR(3) NULL
    , RefName CHAR(20) NULL
    , RefTime DATETIME2(7) NULL
    );

INSERT INTO @TestData
(ID, Reference, RefName, RefTime)
VALUES (1,'abc', 'John', '2016-02-08 08:25:35')
    , (2, 'abc', 'John', '2016-02-08 08:25:37')
    , (3, 'bnm', 'Eliza', '2016-02-08 08:25:39')
    , (4, 'abc', 'John', '2016-02-08 08:25:42')
    , (351, 'cdf', 'Eliza', '2016-06-01 15:25:42')
    , (352, 'abc', 'John', '2016-06-02 15:25:42');

The Query

WITH CTE_FirstPass AS
    (
    SELECT T.ID, T.Reference, T.RefName, T.RefTime
        , LAG(T.ID) OVER (PARTITION BY T.RefName, T.Reference ORDER BY T.RefTime) AS PrevIDForReference
    FROM @TestData AS T
    )
    , CTE_SecondPass AS
    (
    SELECT P.*
        , PriorT.RefTime AS PriorRefTime
        , ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) AS PriorTimeDiff
        , IncludeData = CASE    WHEN P.PrevIDForReference IS NULL THEN 1 
                                WHEN ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) <= 20 THEN 0
                                ELSE 1
                                END                         
    FROM CTE_FirstPass AS P
        LEFT OUTER JOIN @TestData AS PriorT ON PriorT.ID = P.PrevIDForReference AND PriorT.Reference = P.Reference AND PriorT.RefName = P.RefName
    )
    SELECT P.ID
        , P.Reference
        , P.RefName
        , P.RefTime
    FROM CTE_SecondPass AS P
    WHERE P.IncludeData = 1
    ORDER BY P.ID;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.