Gaps and Islands - get a list of dates unemployed over a date range with Postgresl

Question

I have a table called Position, in this table, I have the following, dates are inclusive (yyyy-mm-dd), below is a simplified view of the employment dates

id, person_id, start_date, end_date  , title
1 , 1        , 2001-12-01, 2002-01-31, 'admin'
2 , 1        , 2002-02-11, 2002-03-31, 'admin'
3 , 1        , 2002-02-15, 2002-05-31, 'sales'
4 , 1        , 2002-06-15, 2002-12-31, 'ops'

I'd like to be able to calculate the gaps in employment, assuming some of the dates overlap to produce the following output for the person with id=1

person_id, start_date, end_date  , last_position_id, gap_in_days
1        , 2002-02-01, 2002-02-10, 1               , 10
1        , 2002-06-01, 2002-06-14, 3               , 14

I have looked at numerous solutions, UNIONS, Materialized views, tables with generated calendar date ranges, etc. I really am not sure what is the best way to do this. Is there a single query where I can get this done?

what you mean `assuming some of the dates overlap` ? – Juan Carlos Oropeza Jul 08 '19 at 17:26 — Juan Carlos Oropeza, Jul 08 '19 at 17:26
what is `last_position_id`? – Juan Carlos Oropeza Jul 08 '19 at 17:30 — Juan Carlos Oropeza, Jul 08 '19 at 17:30
a person can have 2 positions hence dates overlap – user1320651 Jul 08 '19 at 17:42 — user1320651, Jul 08 '19 at 17:42
last_position_id is the id of the position before the gap – user1320651 Jul 08 '19 at 17:42 — user1320651, Jul 08 '19 at 17:42

score 2 · Answer 1 · answered Jul 09 '19 at 06:56

step-by-step demo:db<>fiddle

You just need the lead() window function. With this you are able to get a value (start_date in this case) to the current row.

SELECT
    person_id,
    end_date + 1 AS start_date,
    lead - 1 AS end_date,
    id AS last_position_id,
    lead - (end_date + 1) AS gap_in_days
FROM (
    SELECT 
        *,
        lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
    FROM
        positions
) s
WHERE lead - (end_date + 1) > 0

After getting the next start_date you are able to compare it with the current end_date. If they differ, you have a gap. These positive values can be filtered within the WHERE clause.

(if 2 positions overlap, the diff is negative. So it can be ignored.)

Juan Carlos Oropeza · Accepted Answer · 2019-07-08T18:15:46.720

first you need to find what dates overlaps Determine Whether Two Date Ranges Overlap
then merge those ranges as a single one and keep the last id
finally calculate the ranges of days between one end_date and the next start_date - 1

SQL DEMO

with find_overlap as (
  SELECT t1."id" as t1_id, t1."person_id", t1."start_date", t1."end_date",
         t2."id" as t2_id, t2."start_date" as t2_start_date, t2."end_date" as t2_end_date
  FROM Table1 t1
  LEFT JOIN Table1 t2
    ON t1."person_id" = t2."person_id"
   AND t1."start_date" <= t2."end_date"
   AND t1."end_date"   >= t2."start_date"
   AND t1.id < t2.id
), merge_overlap as (
  SELECT 
         person_id,
         start_date,
         COALESCE(t2_end_date, end_date) as  end_date,
         COALESCE(t2_id, t1_id) as last_position_id
  FROM find_overlap
  WHERE t1_id NOT IN (SELECT t2_id FROM find_overlap WHERE t2_ID IS NOT NULL)
), cte as (
  SELECT *, 
         LEAD(start_date) OVER (partition by person_id order by start_date) next_start
  FROM merge_overlap 
) 
SELECT *, 
       DATE_PART('day', 
                  (next_start::timestamp - INTERVAL '1 DAY') - end_date::timestamp
                ) as days 
FROM cte
WHERE next_start IS NOT NULL

OUTPUT

| person_id | start_date |   end_date | last_position_id | next_start | days |
|-----------|------------|------------|------------------|------------|------|
|         1 | 2001-12-01 | 2002-01-31 |                1 | 2002-02-11 |   10 |
|         1 | 2002-02-11 | 2002-05-31 |                3 | 2002-06-15 |   14 |

This assume only simple overlap between 2 dates. If you have a three way overlap maybe need do a recursive query first — Juan Carlos Oropeza, Jul 08 '19 at 18:11
why I got the correct answer. Even when my asnwer is correct S-Man answer seem simpler — Juan Carlos Oropeza, Jul 18 '19 at 14:40

Gaps and Islands - get a list of dates unemployed over a date range with Postgresl

2 Answers2