Check difference in dates on separate SQL rows

Question

I have a table that stores start dates and end dates for sequential periods of an objects progress. This is to track when each object enters the next step of its progress

For example :

ObjectNum | Stage | StartDate | EndDate
1         |1      |2018-01-01 | 2018-02-24
1         |2      |2018-02-25 |
2         |1      |2018-01-01 |

Is there a way in SQL to search this table and find any object, where the difference between the end date of one stage and the start of the next sequential stage is more than 1 day?

Kind regards

Matt

Related: https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows — Hans Keﬆing, Aug 08 '18 at 14:17
Could you also have a problem of overlapping start/end on multiple rows? If so you might need to combine those rows to first find the true start/end then use a technique like jnevill's answer — Matt, Aug 08 '18 at 14:52

JNevill · Accepted Answer · 2018-08-08T14:25:49.457

4

You can join the table to itself on ObjectNum and Stage and then test:

SELECT
    t1.ObjectNum,
    t1.Stage,
    t1.StartDate,
    t1.EndDate,
    DATEDIFF("D", t1.EndDate, t2.StartDate) as StageGapInDays
FROM table t1
        INNER JOIN table t2 
            ON t1.ObjectNum = t2.ObjectNum
                AND t1.Stage + 1 = t2.Stage
Where StageGapInDays > 1

You can also use the LEAD() Window Function in a subquery so you only have to scan the table once and avoid a join:

SELECT *
FROM
    (
        SELECT
            ObjectNum,
            Stage,
            StartDate,
            EndDate,
            DATEDIFF(day, EndDate, LEAD(StartDate) OVER (PARTITION BY ObjectNum ORDER BY Stage)) as StageGapInDays
        FROM table      
    ) subquery
Where StageGapInDays > 1

This second one would catch situations where there is Stage 1, NO Stage 2 and then a Stage 3 where the gap between Stage 1 and Stage 3 is greater than a day. The first query would not catch that scenario.

edited Aug 08 '18 at 14:25

answered Aug 08 '18 at 14:20

JNevill

46,980
4
38
63

Thanks JNevill. I had to use the join as lead can't be used in server 2008 apparently. Hopefully the higher-ups will agree to an upgrade soon! – Matt Bartlett Aug 08 '18 at 15:41
Oh darn! 2008. It does have some window function capability. You *may* be able to replace that LEAD() with `Max(StartDate) OVER (PARTITION BY ObjectNum ORDER BY Stage ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)` I believe that `Max() OVER...` was added in that version. (If you are feeling adventurous. – JNevill Aug 08 '18 at 16:23
I actually ended up using the row_number() feature to put together 2 tables and join with the rows all bumped up one and it seem to do the trick. I've marked yours as an answer because Lead() would have been perfect. I'll add my solution below for those that can't use Lead(). – Matt Bartlett Aug 09 '18 at 09:17

score 0 · Answer 2 · answered Aug 09 '18 at 09:23

Update for those interested in this. Here is the solution I went with given that Lead() does not work in SQL2008

Select ObjectNum from Object m join
       (SELECT ObjectNum, StartDate, EndDate, row_number() over (PARTITION by ObjectNum order by StartDate asc)as Row from STATHIS) s1
       on s1.ObjectNum = m.ObjectNum
       join
       (SELECT ObjectNum, StartDate, EndDate, row_number() over (PARTITION by ObjectNum order by StartDate asc)as Row from STATHIS) s2
       on s1.Row +1 = s2.Row and s1.ObjectNum = s2.ObjectNum
where DATEDIFF(DAY,s1.EndDate,s2.StartDate) > 1

Check difference in dates on separate SQL rows

2 Answers2