>i have table dtr with fields empno, swipedate and ti1 (logical). i want to select all ti1 = .t. for every employee that falls in 3 consecutive days within a week.
>
>say,
>
>0001 12/01/2003 F
>0001 12/02/2003 T
>0001 12/03/2003 T
>0001 12/04/2003 T
>0001 12/05/2003 F
>
>0001 12/06/2003 F
>0001 12/07/2003 F
>
>0001 12/08/2003 T
>0001 12/09/2003 T
>0001 12/10/2003 F
>0001 12/11/2003 T
>0001 12/12/2003 T
>.
>.
>.
>0001 12/31/2003 F
>
>i should be able to select empno 0001 and dates 12/02/2003, 12/03/2003 and 12/04/2003. i should be able to do this per employee. table dtr contains a thousand of different empno.
>
>please advise me..
>
>TIA

Evelyn,

What's the performance of the follwoing three solutions ?

1 - with one select
2 - with two selects
3 - also one select (seems to be the fastest)

*---------------------------------------------------------------------------
function do_it()

	create cursor pp ( ;
		empno		c(4)	default '', ;
		swipedate	D	default {}, ;
		ti1		L	default FALSE ;
	)
	
	insert into pp	values ( '0001', {^2003/12/01}, .F.)
	insert into pp	values ( '0001', {^2003/12/02}, .T.)
	insert into pp	values ( '0001', {^2003/12/03}, .T.)
	insert into pp	values ( '0001', {^2003/12/04}, .T.)
	insert into pp	values ( '0001', {^2003/12/05}, .F.)
	
	insert into pp	values ( '0001', {^2003/12/06}, .F.)
	insert into pp	values ( '0001', {^2003/12/07}, .F.)
	
	insert into pp	values ( '0001', {^2003/12/08}, .T.)
	insert into pp	values ( '0001', {^2003/12/09}, .T.)
	insert into pp	values ( '0001', {^2003/12/10}, .F.)
	insert into pp	values ( '0001', {^2003/12/11}, .T.)
	insert into pp	values ( '0001', {^2003/12/12}, .T.)
	
	index on empno tag empno
	inde on swipedate tag swipedate
	index on ti1 tag ti1
        index on empno+dtos(swipedate) tag xxxx
	
	=Do_it_1()
	=do_it_2()
        =do_it_3()

        

endfunc
*---------------------------------------------------------------------------
function do_it_1()

	
	Select	T1.empno, ;
		T1.swipedate, ;
		T2.swipedate, ;
		T3.swipedate ;
	from pp T1, pp T2, pp T3 ;
	into cursor Result1 ;
	where	( T1.empno == T2.empno ) ;
	and	( T2.empno == T3.empno ) ;
	and	( T1.ti1 ) ;
	and	( T2.ti1 ) ;
	and	( T3.ti1 ) ;
	and	( T2.swipedate == T1.swipedate+1 ) ;
	and	( T3.swipedate == T2.swipedate+1 ) 			
	
endfunc
*---------------------------------------------------------------------------
function do_it_2()
	
	select	empno+dtos(swipedate)	as	xxx, ;
		empno, ;
		swipedate ;
		from pp ;
		into cursor tmp ;
		where	( ti1 )
	
	select tmp
	index on xxx tag xxx
	
	select	T1.empno, ;
		T1.swipedate, ;
		T2.swipedate, ;
		T3.swipedate ;
	from tmp T1, tmp T2, tmp T3 ;
	into cursor Result2 ;
	where	( T2.xxx == T1.empno+dtos(T1.swipedate+1) ) ;
	and	( T3.xxx == T2.empno+dtos(T2.swipedate+1) ) ;
			
endfunc
*---------------------------------------------------------------------------
function do_it_3()

	Select	T1.empno, ;
		T1.swipedate, ;
		T2.swipedate, ;
		T3.swipedate ;
	from pp T1, pp T2, pp T3 ;
	into cursor Result3 ;
	where	( T2.empno+dtos(T2.swipedate) == T1.empno+dtos(T1.swipedate+1) ) ;
	and	( T3.empno+dtos(T3.swipedate) == T2.empno+dtos(T2.swipedate+1) ) ;
	and	( T1.ti1 ) ;
	and	( T2.ti1 ) ;
	and	( T3.ti1 ) ;
					
	
endfunc
*---------------------------------------------------------------------------