～ 參考ORACLE OLAP FUNCTION lag(dt) over (order by dt) 的做法. FROM AskTom ～
建立測試table並新增測試資料
create table t ( a int, b date, c date );
insert into t values(1, DATE('2007-01-01'), date('2007-01-15'));
insert into t values(2, DATE('2007-01-03'), DATE('2007-01-10'));
insert into t values(3, DATE('2007-01-12'), DATE('2007-01-25'));
insert into t values(4, DATE('2007-01-20'), DATE('2007-02-01'));
insert into t values(5, DATE('2007-02-05'), DATE('2007-02-10'));
insert into t values(6, DATE('2007-02-05'), DATE('2007-02-28'));
insert into t values(7, DATE('2007-02-10'), DATE('2007-02-15'));
insert into t values(8, DATE('2007-02-18'), DATE('2007-02-23'));
insert into t values(9, DATE('2007-02-22'), DATE('2007-03-16'));
運用Olap Function及遞迴Sql Query
WITH N(COL,L) AS (
SELECT ROW_NUMBER() OVER() COL,L
FROM (SELECT B + L DAY L FROM T,(SELECT ROW_NUMBER() OVER() - 1 L
FROM SYSIBM.SYSCOLUMNS) K
WHERE B+L DAY <= C
GROUP BY B+L DAY
) TMP
)
SELECT PREV.L + 1 DAY,N.L - 1 DAY FROM N
LEFT JOIN N PREV ON PREV.COL = N.COL - 1
WHERE DAYS(N.L) - DAYS(PREV.L) > 1
FIND GAP
2007/2/2 2007/2/4
