/*  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)