Extract days covered in contiguous period over multiple rows

Question

I have data in an Ingres 10 table like the below :-

ref, from_date, to_date
A, 01/04/2016, 30/04/2016
A, 30/04/2016, 20/05/2016
A, 25/05/2016, 30/05/2016
B, 01/04/2016, 01/09/2016
B, 01/10/2016, 20/02/2016

The to_dates are exclusive-to.

So some sets of lines represent continuous periods with no gaps, these can span multiple lines, however sometimes there are gaps.

I need to count the days covered in each continuous period. I can't think of a way to do this.

So for ref A, I need to know that the first continuous period is 01/04/16 to 20/05/16 and there are 46 days in this period, and that the second continuous period is 25/05/16 - 30/05/16. and this is 4 days.

make a table that has all coninuous days than then left join to this table. rows with null are not contigious — Hogan, Nov 18 '16 at 16:36
I don't understand how I would 'make a table that has all coninuous days ' ? — Ben Hamilton, Nov 18 '16 at 17:20
The same way you make any other table, insert statements -- or you could script it. — Hogan, Nov 18 '16 at 19:18
It's the 'all continuous days' but I don't quite get...exactly what would be inserted into a temp table? — Ben Hamilton, Nov 22 '16 at 07:40

score 1 · Accepted Answer · answered Nov 18 '16 at 20:06

I can't think of a clever way to do this in one SQL statement. The answer that follows relies on creating a copy of the table and iteratively updating it until we have an 'earliest continuous from date' for each period. Then it's just a question of selecting the largest period for each ref and from date combination.

In the example below I've run the update twice, however with arbitrary data you'd need to run it until the update updates 0 rows.

Also I've taken the liberty of fixing what are I assume a couple of typos in the dates.

DECLARE GLOBAL TEMPORARY TABLE SESSION.test2 AS
SELECT ref AS ref,
  from_date AS from_date,
  to_date AS to_date,
  from_date AS cont_from
FROM test
ON COMMIT PRESERVE ROWS
WITH NORECOVERY
Executing . . .

(5 rows)
continue
* * SELECT * FROM SESSION.test2
Executing . . .


+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |30-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
* * * * * * * * * * *
/* repeat this update until 0 rows are updated */
UPDATE SESSION.test2 b
FROM SESSION.test2 a
SET cont_from = a.cont_from
WHERE b.cont_from <= a.to_date
  AND b.cont_from >  a.from_date
  AND b.ref = a.ref
  AND b.cont_from != a.cont_from;
SELECT * FROM SESSION.test2
Executing . . .

(1 row)

+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |01-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
*
/* repeat this update until 0 rows are updated */
UPDATE SESSION.test2 b
FROM SESSION.test2 a
SET cont_from = a.cont_from
WHERE b.cont_from <= a.to_date
  AND b.cont_from >  a.from_date
  AND b.ref = a.ref
  AND b.cont_from != a.cont_from;
SELECT * FROM SESSION.test2
Executing . . .

(0 rows)

+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |01-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
* * * * * SELECT ref,cont_from,MAX(to_date - cont_from)
FROM SESSION.test2
GROUP BY ref,cont_from
ORDER BY 1,2
Executing . . .


+------+-------------------------+-------------------------+
|ref   |cont_from                |col3                     |
+------+-------------------------+-------------------------+
|A     |01-apr-2016              |49 days                  |
|A     |25-may-2016              |5 days                   |
|B     |01-apr-2016              |153 days                 |
|B     |01-oct-2016              |142 days                 |
+------+-------------------------+-------------------------+

HTH

There is a way to find continuous days that is simple and well understood. Here is a nice article which explores the problem: https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/ It has also been beaten to death here on SO and this should probably be marked as a duplicate — Hogan, Nov 22 '16 at 16:30
Worth pointing out that that article is based on SQL Server. Ingres doesn't support all the SQL syntax that appears in some of the solutions. I think solution #1 would still work though. — PaulM, Nov 27 '16 at 17:50

Extract days covered in contiguous period over multiple rows

1 Answers1