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.
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