Cetin,

Guess I won't be able to beat to 26 secs then


>Oh Gregory,
>Thanks for your reply. I feel guilty stealing your time :(
>Tried it with an 'escape on' flag and went to take a coffee. On return it was still running. Stopped it after sometime and passed time was 306 secs. (was on 2nd SQL from bottom).
>I ended with a routine that's almost identical to the routine in reply to myself (26-27 secs). Sorry couldn't mark that as a solution as I didn't want to mark myself (even don't know if it's applicable).
>Later I thought on it more and if I can't speed up this part I could gain the time from visual slowness of human :) and divided the original cursor retrieval into parts, getting and showing as needed. Kind of pseudo fetchasneeded OTOH complex enough not to publish :(
>Cetin
>
>>>I have data with this structure :
>>>
>>>

>>>checkdate  xclusive id1   id2   nstart  nend
>>>---------- -------- ---   ---   ------  ----
>>>01/01/2003     F     1     1      96     120
>>>01/01/2003     F     1     1     108     120
>>>01/01/2003     F     1     1     108     120
>>>01/01/2003     F     1     1     108     120
>>>
>>>01/01/2003     F     2     1      96     108
>>>01/01/2003     F     2     1     102     120
>>>01/01/2003     F     2     1     102     120
>>>
>>>01/02/2003     F     2     1      96     108
>>>01/02/2003     F     2     1      96     132
>>>01/02/2003     F     2     1     102     108
>>>01/02/2003     F     2     1     102     120
>>>01/02/2003     F     2     1     102     120
>>>

>>>Start,End are in the range 1,186.
>>>
>>>What I need is counts for distinct Date,Start,End (ids and xcl matter). There are overlaps in Start/End and I need to split them.
>>>
>>>Above samples counts should be as :
>>>

>>>checkdate  xclusive id1   id2   nstart  nend  cnts
>>>---------- -------- ---   ---   ------  ----  ----
>>>01/01/2003     F     1     1      96     108    1
>>>01/01/2003     F     1     1     108     120    4
>>>
>>>01/01/2003     F     2     1      96     102    1
>>>01/01/2003     F     2     1     102     108    3
>>>01/01/2003     F     2     1     108     120    2
>>>
>>>01/02/2003     F     2     1      96     102    2
>>>01/02/2003     F     2     1     102     108    5
>>>01/02/2003     F     2     1     108     120    3
>>>01/02/2003     F     2     1     120     132    1
>>>

>>>
>>>Any ideas on a fast routine to do this. TIA.
>>>Cetin
>>
>>Cetin,
>>
>>Your problem has puzzled me since a few days.
>>
>>How does this work for you and if it does, what is the speed ?
>>
>>

>>function ppdo_it()
>>
>>	create cursor test ;
>>	(	Checkdate	D		default {}, ;
>>		Excl		c(1)	default '', ;
>>		Id1			I		default 0, ;
>>		Id2			I		default 0, ;
>>		nStart		I		default 0, ;
>>		nEnd		I		default 0 ;
>>	)
>>	insert into Test values({01/01/2003}, 'F', 1, 1, 96, 120 )
>>	insert into Test values({01/01/2003}, 'F', 1, 1, 108, 120 )
>>	insert into Test values({01/01/2003}, 'F', 1, 1, 108, 120 )
>>	insert into Test values({01/01/2003}, 'F', 1, 1, 108, 120 )
>>	
>>	insert into Test values({01/01/2003}, 'F', 2, 1, 96, 108 )
>>	insert into Test values({01/01/2003}, 'F', 2, 1, 102, 120 )
>>	insert into Test values({01/01/2003}, 'F', 2, 1, 102, 120 )
>>	
>>	insert into Test values({01/02/2003}, 'F', 2, 1, 96, 108 )
>>	insert into Test values({01/02/2003}, 'F', 2, 1, 96, 132 )
>>	insert into Test values({01/02/2003}, 'F', 2, 1, 102, 108 )
>>	insert into Test values({01/02/2003}, 'F', 2, 1, 102, 120 )
>>	insert into Test values({01/02/2003}, 'F', 2, 1, 102, 120 )
>>*!*		
>>	&& Need Collate Machine
>>	local sCollate
>>	sCollate = set('Collate')
>>	set Collate to 'Machine'
>>	
>>	&& (1) Speed
>>	select	dtos(CheckDate) + excl + bintoc(Id1) + bintoc(Id2)	as tmpGroup, ;
>>			recno()	as rec, ;
>>			CheckDate, ;
>>			Excl, ;
>>			Id1, ;
>>			Id2, ;
>>			nStart, ;
>>			nEnd ;
>>		from Test ;
>>		into cursor Test1 ;
>>		order by 1
>>
>>		
>>	&& (2) All lines start at nStart
>>	&&    intermediate stops at all (nStart, nEnd ) where (nStart, nEnd) <= nEnd
>>	select	d1.tmpGroup, ;
>>			d1.rec, ;
>>			d1.CheckDate, ;
>>			d1.Excl, ;
>>			d1.Id1, ;
>>			d1.Id2, ;
>>			d1.nStart, ;
>>			d1.nEnd, ;
>>			d2.nStart	as Intermediate ;
>>		from test1 d1, Test1 d2 ;
>>		into cursor test2 ;
>>		where	( d1.tmpGroup == d2.TmpGroup ) ;
>>			and	( d2.nStart between d1.nStart and d1.nEnd ) ;
>>	union ;
>>	select	d1.tmpGroup, ;
>>			d1.rec, ;
>>			d1.CheckDate, ;
>>			d1.Excl, ;
>>			d1.Id1, ;
>>			d1.Id2, ;
>>			d1.nStart, ;
>>			d1.nEnd, ;
>>			d2.nEnd		as Intermediate ;
>>		from test1 d1, Test1 d2 ;
>>		where	( d1.tmpGroup == d2.TmpGroup ) ;
>>			and	( d2.nEnd between d1.nStart and d1.nEnd ) ;
>>	
>>	&& add recno for upcoming self join	
>>	select	tmpGroup as tmpGroup, ;
>>			rec, ;
>>			CheckDate, ;
>>			Excl, ;
>>			Id1, ;
>>			Id2, ;
>>			nStart, ;
>>			nEnd, ;
>>			Intermediate, ;
>>			recno()	as nrec ;
>>		from Test2 ;
>>		into cursor Test3 ;
>>	
>>	&&
>>	select	d1.tmpGroup, ;
>>			d1.rec, ;
>>			d1.CheckDate, ;
>>			d1.Excl, ;
>>			d1.Id1, ;
>>			d1.Id2, ;
>>			d1.nStart, ;
>>			d1.nEnd, ;
>>			d1.nRec, ;
>>			d2.nrec, ;
>>			d1.Intermediate	as x1, ;
>>			d2.Intermediate	as x2 ;
>>		from test3 d1, Test3 d2 ;
>>		into cursor test4 ;
>>		where	( d1.tmpGroup == d2.TmpGroup ) ;
>>			and	( d1.rec == d2.rec ) ;
>>			and	( d1.nrec+1 == d2.nrec ) ;
>>		order by 1, 2, x1, x2
>>		
>>
>>	&& Final
>>	select	CheckDate, ;
>>			Excl, ;
>>			Id1, ;
>>			Id2, ;
>>			x1	as	nStart, ;
>>			x2	as	nEnd, ;
>>			cnt(*)	as	cnts ;
>>		from Test4 ;
>>		into cursor Result ;
>>		group by tmpGroup, 5, 6
>>	
>>	set Collate to (sCollate)
>>
>>endfunc
>>*---------------------------------------------------------------------------
>>