/* Advanced SQL/Sybase Fall 2002 */ /* Gaps in a time series */ /* */ /* Rewrite of SQL statements pp. 78-79 in Celko's book to */ /* run on Syabase Adaptive Server Enterprise. */ /* */ 1> use evelyn 2> go 1> select * from jobapps 2> go candidate jobtitle startdate enddate ------------------------- ---------- -------------------------- -------------------------- John Smith Vice Pres Jan 10 1989 12:00AM Dec 31 1989 12:00AM John Smith President Jan 12 1990 12:00AM Dec 31 1991 12:00AM Bill Jones Scut Worke Feb 24 1990 12:00AM Apr 21 1990 12:00AM Bill Jones Manager Jan 1 1991 12:00AM Jan 5 1991 12:00AM Bill Jones Grand Poob Apr 4 1991 12:00AM May 15 1991 12:00AM /* Note (in addition to rewritten date functions): */ /* Statement is a self-join. */ /* */ /* This query shows too many gaps when more than 2 intervals. */ 2> 3> select j1.candidate, dateadd(dd, 1, j1.enddate) gapstart, 4> dateadd(dd, -1, j2.startdate) gapend, 5> datediff(dd, j1.enddate, j2.startdate) gaplength 6> from jobapps j1, jobapps j2 7> where j1.candidate = j2.candidate 8> and dateadd(dd, 1, j1.enddate) < = dateadd(dd, -1, j2.startdate) 9> go candidate gapstart gapend gaplength ------------------------- -------------------------- -------------------------- ----------- John Smith Jan 1 1990 12:00AM Jan 11 1990 12:00AM 12 Bill Jones Apr 22 1990 12:00AM Dec 31 1990 12:00AM 255 Bill Jones Apr 22 1990 12:00AM Apr 3 1991 12:00AM 348 Bill Jones Jan 6 1991 12:00AM Apr 3 1991 12:00AM 89 (4 rows affected) /* */ /* Instead, use only most recently ended job for gap (max). */ /* Query built with two self-joins, one in a subquery. */ /* */ 2> 3> select j1.candidate, dateadd(dd, 1, j1.enddate) gapstart, 4> dateadd(dd, -1, j2.startdate) gapend, 5> datediff(dd, j1.enddate, j2.startdate) gaplength 6> from jobapps j1, jobapps j2 7> where j1.candidate = j2.candidate 8> and dateadd(dd, 1, j1.enddate) = 9> (select max(dateadd(dd, 1, j3.enddate)) 10> from jobapps j3 11> where j3.candidate = j1.candidate 12> and dateadd(dd, 1, j3.enddate) < = dateadd(dd, -1, j2.startdate)) 13> go candidate gapstart gapend gaplength ------------------------- -------------------------- -------------------------- ----------- John Smith Jan 1 1990 12:00AM Jan 11 1990 12:00AM 12 Bill Jones Apr 22 1990 12:00AM Dec 31 1990 12:00AM 255 Bill Jones Jan 6 1991 12:00AM Apr 3 1991 12:00AM 89 (3 rows affected)