It's weird because the file I have is 7MB but only the 65K rows and I can't open it in a text editor because it is unicode.

10 minutes is on...

Your data came across as unicode and only 65K rows. Looks like it was save from excel maybe?

The first iteration took 10 minutes

You have some Nulls in your data. Should they be set to zero?

Ok, so I ended up with building 3 identical tables.

wells holds the initial data you provided

wellsa gets the first aggregate set

wellsb gets the aggregated set from wellsa

I bounce back and...

Here is a fix but it will add iterations since we will work with the first set from each wellnum at a time.

select holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag from (

select

a.holeid,a.evalnum,c.seq

/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length...

For welnum4 seq 19 I'm having an issue where it is aggregating with seq 17 and 18 and also with seq 20 and 21 simultaneously. I haven't figure out...

The first part finds all of the records that we want to aggregate and joins them to the previous record and next record. Having all three records in a...

I just found an error in my code where I reversed the basedepth and topdepth in the union. I edited the post above to correct this.

It only took 1 second on the test data you gave me and that was without any indexes on the table. To run multiple iterations I think we will...

This adds the new aggregated records into the other records.

select * from (

select

a.holeid,a.evalnum,c.seq

/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/

,case when case when a.basedepth > b.basedepth then a.basedepth else...

Let's try this

select

a.holeid,a.evalnum,c.seq

/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/

,case when case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end > c.basedepth then case when a.basedepth > b.basedepth...

Here is what I have so far to gather the records that need to be aggregated. How do I determine the lenght weighed average of the MBit column?

select a.*,b.seq...

For "bound by" can we assume the sequence number is +1 and -1 for bound records?

Can you also provide a create table script?

THe picture is a broken link. Please provide the table structure, small set of sample records, and an example of your expected results.