CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
number005
number005

Reputation: 27

Find "interruption" in dates with SQL

I have a table with fields: VALID_FROM - VALID_UP - CLIENT

Lets say

 2005 - 2006 - Smith
 2006 - 2009 - Smith
 2010 - 2013 - Smith
 2013 - 2014 - Smith
 2015 - 2016 - Smith
 2016 - 2017 - Smith
 2017 - today - Smith
 2014 - today - Bob

What I want to find is the start date after the interruption. In this case:

2015 - 2016 - Smith
2014 - today - Bob

There might me hundreds of entries - I always need the FIRST interruption, going back from today.

Any chance I can do this in (Oracle) SQL?

Upvotes: 1

Views: 193

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

One approach here is to use the LAG() analytic function to check each row for an interrupted date, for each client. Then, subquery this to restrict to the latest interruption only. It would be difficult to explain the query below in words, but if you run each piece, starting with the innermost subquery and building up outwards, it should make sense to you.

SELECT
    t.VALID_FROM,
    t.VALID_UP,
    t.CLIENT
FROM
(
    SELECT t.*,
        ROW_NUMBER() OVER (PARTITION BY CLIENT ORDER BY VALID_FROM DESC) rn
    FROM
    (
        SELECT t.*,
            LAG(VALID_UP) OVER (PARTITION BY CLIENT ORDER BY VALID_FROM) v_lag
        FROM yourTable t
    ) t
    WHERE t.VALID_FROM <> COALESCE(t.v_lag, -1)
) t
WHERE t.rn = 1;

Output:

enter image description here

I can never seem to get Rextester working for Oracle, but I did manage to get a demo working for SQL Server:

Demo

Upvotes: 3

Related Questions