How to compare datetime NextRow with CurrentRow in SQL and find a difference in X amount of days?

Question

This is my current table structure (MS SQL 10):

CaseNo     EntryDate                    Comment
0000000001     2013-10-10 12:00:00.000      This is a test comment
0000000001     2013-10-21 18:00:00.000      This is a test comment
0000000002     2013-10-10 12:00:00.000      This is a test comment
0000000003     2013-11-07 12:00:00.000      This is a test comment
0000000003     2013-12-01 02:00:00.000      This is a test comment
0000000003     2013-12-01 09:00:00.000      This is a test comment
0000000004     2013-10-10 12:00:00.000      This is a test comment

Expected output:

CaseNo
0000000001
0000000003

What I'm looking for is to get a list of CaseNos where the activity (Comment) between entry dates is more than 7 days apart (one instance is enough).

This is where I'm currently at (taken from Date Difference between consecutive rows):

SELECT  T1.CaseNo, 
    T1.Operator, 
    T1.EntryDate, 
    MIN(T2.EntryDate) AS NextDate, 
    DATEDIFF("D", T1.EntryDate, MIN(T2.EntryDate)) AS DaysDiff
FROM    CaseCorrespondence T1
    LEFT JOIN Cases T2
        ON T1.CaseNo = T2.CaseNo
        AND T2.EntryDate > T1.EntryDate
GROUP BY T1.CaseNo, T1.Operator, T1.EntryDate
ORDER BY T1.CaseNo;

The problem with this query is that I'm not currently calculating where T1.EntryDate is older than x days. The DaysDiff column will tell me how many days exist between rows, but I cannot seem to add a WHERE clause to this to specify > 7 days. (WHERE DaysDiff > 7) for example

Since you are comparing dates, it would be beneficial to know which database you are using. — FutbolFan, Aug 31 '15 at 20:24
Sounds like you are using `SQL Server 2008`, so unfortunately, you won't be able to take advantage of the new analytic functions like `Lead` and `Lag` functions which was introduced in `SQL Server 2012`. But you could use `row_number` instead to do what you need. — FutbolFan, Aug 31 '15 at 20:44

score 1 · Accepted Answer · answered Aug 31 '15 at 20:38

Create sample table:

create table cases (CaseNo varchar(50),    EntryDate datetime,    Comment varchar(255));

insert into cases values
('0000000001', '2013-10-10 12:00:00.000', 'This is a test comment'),
('0000000001', '2013-10-21 18:00:00.000', 'This is a test comment'),
('0000000002', '2013-10-10 12:00:00.000', 'This is a test comment'),
('0000000003', '2013-11-07 12:00:00.000', 'This is a test comment'),
('0000000003', '2013-12-01 02:00:00.000', 'This is a test comment'),
('0000000003', '2013-12-01 09:00:00.000', 'This is a test comment'),
('0000000004', '2013-10-10 12:00:00.000', 'This is a test comment');

You could use ROW_NUMBER() to do this:

;WITH q1
AS (
    SELECT row_number() OVER (
            PARTITION BY caseno ORDER BY caseno
                ,entrydate
            ) AS rn
        ,c.*
    FROM cases c
    )
SELECT q1.caseno
FROM q1
INNER JOIN q1 q2 ON q1.caseno = q2.caseno
    AND q1.rn = q2.rn + 1
WHERE datediff(day, q2.entrydate, q1.entrydate) > 7

Result:

+------------+
|   caseno   |
+------------+
| 0000000001 |
| 0000000003 |
+------------+

SQL Fiddle Demo

How to compare datetime NextRow with CurrentRow in SQL and find a difference in X amount of days?

1 Answers1