Thank you Gail....I will read through these.

thanks Steve.......didn't mean to call you CRAZY!!!! Picked up the wrong name.

Thank you Gail and SSCrazy for the advice.

As far as the additional request -

THere could be 5m rows

156 dates (3 yrs)

50,000 different items.

The query distribution is estimated at

1/3 combined...

Thanks for the response. Haven't got back to it but i do follow your suggestion. I'll let you know how i make out.

Trying to be proactive.

I have left out some detail. Let me give the full scenario:

Sales transaction database. 100's millions of rows. I built an access front end for users to...

Thanks for the response. I like hearing from smart people!!!

My thought process was due to the table would carry records that spanned 18 months and again possibly 500 million rows....

no, can't with profiler.

2 minutes ago i gave it a shot with excel and it opened just fine. It is data in column and rows. I have no idea what...

2 execution plans attached

WithCTE

NoCTE

Both produce the same results.

WithCTE - 2 mins 46 seconds

NoCTE - 42 seconds

the index .....SWT is the SKU/WK/TRANSID index with untis as an uncluded column.

OK..I'm back.

I'm not going to include any execution plans because i think your work is done and done quite well. My 4 1/2 minutes query is now 2 minutes plus...

I will do as you both suggest and then run the query with the execution plan.

Big table so it may take a little while to create the index.

I'll report back...

Now that i understand the communication better, let me give the scenario the way i should have started.

TABLE: DETAIL SALES TRANSACTIONS

SKU_NUM - PK & single column index

TRANS_ID - PK &...

Hi

#1 - thanks for the input so far guys.

#2 - as i'm sure you figured out, i am far from a dba, and i don't even play a good one...

table: TRN.TRNDTL_SPR09

trans_id

loc_num

sku_num

sls_units

sls_amt

mgn_amt

cld_dt

wk_end_dt

PK- trans_id, sku_num

IDX - one each on: trans_id; cld_dt; loc_num; sku_num; wk_end_dt;

execution plan attached.

Just saw Gila's

I'll follow the instructions and post.

i have not posted a plan before.....what's the best way to do that??