CODEWITHSUNDEEP
sql-servert-sql
Praveen Kumar
Praveen Kumar

Reputation: 13

Finding the Datediff between Records in same Table

IP             QID  ScanDate                Rank
101.110.32.80   6   2016-09-28 18:33:21.000 3
101.110.32.80   6   2016-08-28 18:33:21.000 2
101.110.32.80   6   2016-05-30 00:30:33.000 1

I have a Table with certain records, grouped by Ipaddress and QID.. My requirement is to find out which record missed the sequence in the date column or other words the date difference is more than 30 days. In the above table date diff between rank 1 and rank 2 is more than 30 days.So, i should flag the rank 2 record.

Upvotes: 1

Views: 1054

Answers (3)

JNevill
JNevill

Reputation: 50034

While Window Functions could be used here, I think a self join might be more straight forward and easier to understand:

SELECT
t1.IP,
    t1.QID,
    t1.Rank,
    t1.ScanDate as endScanDate,
    t2.ScanDate as beginScanDate,
    datediff(day, t2.scandate, t1.scandate) as scanDateDays     
FROM
    table as t1
    INNER JOIN table as t2 ON
        t1.ip = t2.ip
        t1.rank - 1 = t2.rank --get the record from t2 and is one less in rank
WHERE datediff(day, t2.scandate, t1.scandate) > 30 --only records greater than 30 days

It's pretty self-explanatory. We are joining the table to itself and joining the ranks together where rank 2 gets joined to rank 1, rank 3 gets joined to rank 2, and so on. Then we just test for records that are greater than 30 days using the datediff function.

Upvotes: 1

Kilren
Kilren

Reputation: 415

I would use windowed function to avoid self join which in many case will perform better.

WITH    cte
          AS (
              SELECT
                t.IP
              , t.QID
              , LAG(t.ScanDate) OVER (PARTITION BY t.IP ORDER BY T.ScanDate) AS beginScanDate
              , t.ScanDate AS endScanDate
              , DATEDIFF(DAY,
                         LAG(t.ScanDate) OVER (PARTITION BY t.IP ORDER BY t.ScanDate),
                         t.ScanDate) AS Diff
              FROM
                MyTable AS t
             )
    SELECT
        *
    FROM
        cte c
    WHERE
        Diff > 30;

Upvotes: 0

neer
neer

Reputation: 4082

You can use LAG in Sql 2012+

declare @Tbl Table (Ip VARCHAR(50), QID INT, ScanDate DATETIME,[Rank] INT)

INSERT INTO @Tbl        
VALUES  
('101.110.32.80', 6,   '2016-09-28 18:33:21.000', 3),
('101.110.32.80', 6,   '2016-08-28 18:33:21.000', 2),
('101.110.32.80', 6,   '2016-05-30 00:30:33.000', 1)

;WITH Result
AS
(
    SELECT
        T.Ip ,
        T.QID ,
        T.ScanDate ,
        T.[Rank],
        LAG(T.[Rank]) OVER (ORDER BY T.[Rank]) PrivSRank,
        LAG(T.ScanDate) OVER (ORDER BY T.[Rank]) PrivScanDate
    FROM
        @Tbl T
) 

SELECT
    R.Ip ,
    R.QID ,
    R.ScanDate ,
    R.Rank ,
    R.PrivScanDate,
    IIF(DATEDIFF(DAY, R.PrivScanDate, R.ScanDate) > 30, 'This is greater than 30 day. Rank ' + CAST(R.PrivSRank AS VARCHAR(10)), '') CFlag
FROM
    Result R

Result:

Ip                       QID         ScanDate                Rank        CFlag
------------------------ ----------- ----------------------- ----------- --------------------------------------------
101.110.32.80            6           2016-05-30 00:30:33.000 1           
101.110.32.80            6           2016-08-28 18:33:21.000 2           This is greater than 30 day. Rank 1
101.110.32.80            6           2016-09-28 18:33:21.000 3           This is greater than 30 day. Rank 2

Upvotes: 1

Related Questions