Hello,

We just had a meeting a few months ago given by a DBA to us developers...

Big advice to us was to take the Items in a WHERE Clause (if you can), apply the logic to JOINS.

Something like this:

SELECT

Live.dbo.sales_fact.trans_ref,

Live.dbo.product_dim.pd_key,

Live.dbo.product_dim.pd_desc,

Live.dbo.multibuy_dim.mb_deal_number,

Live.dbo.sales_fact.LoyaltyNo,

sum(Live.dbo.sales_fact.qty),

sum(Live.dbo.sales_fact.totalPriceExVat),

( sum(Live.dbo.sales_fact.company_margin) ) - isnull((( sum(Live.dbo.sales_fact.wastageval) )),0)

FROM

Live.dbo.multibuy_dim INNER JOIN Live.dbo.sales_fact ON (Live.dbo.sales_fact.multibuy_id=Live.dbo.multibuy_dim.mb_id)

INNER JOIN Live.dbo.product_dim ON (Live.dbo.sales_fact.product_id=Live.dbo.product_dim.pd_id)

INNER JOIN Live.dbo.date_dim ON (Live.dbo.date_dim.date_id=Live.dbo.sales_fact.date_id)

AND ( Live.dbo.date_dim.date ) between DATEADD(WEEK, -3, (select min(date) from date_dim where last_week_prior_flag = 'Y'))

AND (select max(date) from Live.dbo.date_dim where Live.dbo.date_dim.last_week_prior_flag= 'Y' )

WHERE

(

Live.dbo.product_dim.pd_key IN ('481005', '850972', '860091', '860101', '860110', '860130', '860159', '860161', '860211', '860224', '860225', '860230', '860265', '860319', '860320', '860344', '860360', '860407', '860414', '860415', '860418', '860469', '860478', '871005', '874115', '874206', '880100', '881153', '890061', '890299', '890360', '890648', '890650', '890651', '891903')

AND

(

Live.dbo.multibuy_dim.mb_deal_number = '0000'

OR

Live.dbo.multibuy_dim.mb_deal_number Is Null

)

)

GROUP BY

Live.dbo.sales_fact.trans_ref,

Live.dbo.product_dim.pd_key,

Live.dbo.product_dim.pd_desc,

Live.dbo.multibuy_dim.mb_deal_number,

Live.dbo.sales_fact.LoyaltyNo