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