I found this query on older thread and i can not uderstand output interval
pairs:
How to find min and max values in date intervals:
--------------------------------------------------
Input:
CREATE TABLE INTERVALS
(key CHAR(5) NOT NULL
,level CHAR(7) NOT NULL
,date_from DATE NOT NULL
,date_to DATE NOT NULL
);
INSERT INTO INTERVALS
VALUES
('key1', 'level1', '2001-05-01', '2002-10-01')
,('key1', 'level1', '2001-01-01', '2001-04-30')
,('key1', 'level1', '2000-11-10', '2000-12-31')
,('key1', 'level1', '2000-06-01', '2000-10-09')
,('key1', 'level1', '2000-01-01', '2000-05-31')
;
SELECT l.key, l.level, l.date_from, r.date_to
FROM Intervals l
Intervals r,
WHERE NOT EXISTS
(SELECT *
FROM Intervals le
WHERE le.date_to = l.date_from - 1 DAY)
AND r.date_to =
(SELECT MIN(date_to)
FROM Intervals rm
WHERE rm.date_to l.date_from
AND NOT EXISTS
(SELECT *
FROM Intervals re
WHERE re.date_from = rm.date_to + 1 DAY));
or
SELECT l.key, l.level, l.date_from
, (SELECT MIN(date_to)
FROM Intervals rm
WHERE rm.date_to l.date_from
AND NOT EXISTS
(SELECT *
FROM Intervals re
WHERE re.date_from = rm.date_to + 1 DAY)
) AS date_to
FROM Intervals l
WHERE NOT EXISTS
(SELECT *
FROM Intervals le
WHERE le.date_to = l.date_from - 1 DAY);
Same output:
KEY LEVEL DATE_FROM DATE_TO
----- ------- ---------- ----------
key1 level1 2000-11-10 2002-10-01
key1 level1 2000-01-01 2000-10-09
2 record(s) selected.
What is the meaning of this output;
The min value in intervals are
2001-01-01
The max value in intervals are
2002-10-01
Please help
Thank's
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200810/1