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 )
>*!*
>
> local sCollate
> sCollate = set('Collate')
> set Collate to 'Machine'
>
>
> 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
>
>
>
>
> 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 ) ;
>
>
> 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
>
>
>
> 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
>*---------------------------------------------------------------------------
>
